1934. Confirmation Rate
SQL 50 · Basic Joins (day 2). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
One user, including users with no requests.
2. Approach
LEFT JOIN Confirmations, GROUP BY user_id, ROUND(IFNULL(AVG(action = 'confirmed'), 0), 2).LEFT JOIN Confirmations, GROUP BY user_id, ROUND(COALESCE(AVG((action = 'confirmed')::int), 0), 2).
3. The trap
Don’t filter confirmed rows in WHERE; that breaks the denominator.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
SELECT s.user_id, ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id = c.user_id GROUP BY s.user_id;SELECT s.user_id, ROUND(COALESCE(AVG((c.action = 'confirmed')::int), 0), 2) AS confirmation_rate FROM Signups s LEFT JOIN Confirmations c ON s.user_id = c.user_id GROUP BY s.user_id;