1934. Confirmation Rate : leetcode

user_id	time_stamp	action
3	2021-01-06 03:30:46	timeout
3	2021-07-14 14:00:00	timeout
7	2021-06-12 11:57:29	confirmed
7	2021-06-13 12:58:28	confirmed
7	2021-06-14 13:59:27	confirmed
2	2021-01-22 00:00:00	confirmed
2	2021-02-28 23:59:59	timeout

# action : string → integer
SELECT  user_id, 
        (CASE
        WHEN action = 'timeout' THEN 0
        ELSE 1 END) AS action
FROM    Confirmations;

user_id	action
3	0
3	0
7	1
7	1
7	1
2	1
2	0

# AVG(action)
SELECT  user_id, AVG(action)
FROM
(SELECT  user_id, 
        (CASE
        WHEN action = 'timeout' THEN 0
        ELSE 1 END) AS action
FROM    Confirmations) as confirm_int
GROUP BY user_id;

user_id	AVG(action)
3	0
7	1
2	0.5

answer

SELECT s.user_id, ROUND(IFNULL(c.confirmation_rate, 0), 2) as confirmation_rate
FROM Signups s LEFT JOIN
    (SELECT  user_id, AVG(action) as confirmation_rate
    FROM
    (SELECT  user_id, 
            (CASE
            WHEN action = 'timeout' THEN 0
            ELSE 1 END) AS action
    FROM    Confirmations) AS c1
    GROUP BY user_id) as c
        ON s.user_id = c.user_id;

user_id	confirmation_rate
6	0
3	0
7	1
2	0.5