SQL Interview Questions – Confirmation Rate

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

Solution –

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