Calculate User Confirmation Rate in SQL

Question

Given `signups` (user_id) and `confirmations` (user_id, action where action is 'confirmed' or 'timeout'), find the confirmation rate of each user rounded to 2 decimal places. Users who did not request any confirmation messages should have a confirmation rate of 0.00.

Accepted Answer

Optimal Solution:
SELECT 
  s.user_id,
  ROUND(
    COALESCE(AVG(CASE WHEN c.action = 'confirmed' THEN 1.0 ELSE 0.0 END), 0),
    2
  ) AS confirmation_rate
FROM signups s
LEFT JOIN confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id
ORDER BY s.user_id ASC;

Technical Explanation:
A LEFT JOIN preserves users with zero confirmation records. Using `AVG(CASE WHEN c.action = 'confirmed' THEN 1.0 ELSE 0.0 END)` computes the ratio directly, and `COALESCE(..., 0)` handles users with zero records.

user_id	action
3	timeout
3	confirmed
7	confirmed
2	NULL

user_id	confirmation_rate
2	0
3	0.5
7	1