1633. Percentage of Users Attended a Contest : leetcode

# combine Users table and Distinct contest_id
SELECT u.user_id, r.contest_id
FROM Users u CROSS JOIN 
    (SELECT DISTINCT contest_id FROM Register) as r

user_id	contest_id
7	215
2	215
6	215
7	209
2	209
6	209
7	208
2	208
6	208
7	210
2	210
6	210
7	207
2	207
6	207

# combine with Register table including NULL
SELECT u.user_id, c.contest_id, r.user_id
FROM Users u CROSS JOIN 
    (SELECT DISTINCT contest_id FROM Register) as c
LEFT JOIN Register r 
    ON u.user_id = r.user_id AND r.contest_id = c.contest_id;

user_id	contest_id	user_id
7	215	7
2	215	null
6	215	6
7	209	7
2	209	2
6	209	6
7	208	7
2	208	2
6	208	6
7	210	7
2	210	2
6	210	6
7	207	null
2	207	2
6	207	null

answer

SELECT  c.contest_id, 
        ROUND((SUM(CASE WHEN r.user_id IS NOT NULL THEN 1
        ELSE 0 END) / COUNT(*) * 100), 2) as percentage
FROM Users u CROSS JOIN 
    (SELECT DISTINCT contest_id FROM Register) as c
LEFT JOIN Register r 
    ON u.user_id = r.user_id AND r.contest_id = c.contest_id
GROUP BY c.contest_id
ORDER BY percentage DESC, c.contest_id ASC;

contest_id	percentage
208	100
209	100
210	100
215	66.67
207	33.33

better answer

SELECT  contest_id,
        ROUND(
            COUNT(DISTINCT user_id ) * 100 / (SELECT COUNT(user_id) FROM Users), 2
        ) AS percentage
FROM
    Register
GROUP BY
    contest_id
ORDER BY
    percentage DESC,
    contest_id;