Problem Description –
The confirmation rate of a user is the number of
'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is
0. Round the confirmation rate to two decimal places.
Write an SQL query to find the confirmation rate of each user.
Return the result table in any order.
The query result format is in the following example.
Problem Link – Confirmation rate
Difficulty Level – Medium
SELECT s.user_id, ROUND(IFNULL(SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END),0) / COUNT(*),2) as confirmation_rate FROM Signups as s LEFT JOIN confirmations as c ON s.user_id = c.user_id GROUP BY 1