Confirmation Rate

Task: Find the confirmation rate of each user (confirmed / total requests). Round to 2 decimal places.

Table: Signups

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| user_id     | INT      |
| time_stamp  | datetime |
+-------------+----------+

Table: Confirmations

+-------------+-------------+
| Column Name | Type        |
+-------------+-------------+
| user_id     | INT         |
| time_stamp  | datetime    |
| action      | varchar(20) |
+-------------+-------------+

Input: Signups table:

+---------+---------------------+
| user_id | time_stamp          |
+---------+---------------------+
| 3       | 2020-03-21 10:16:13 |
| 7       | 2020-01-04 13:57:59 |
| 2       | 2020-07-29 23:09:44 |
| 6       | 2020-12-09 10:39:37 |
+---------+---------------------+

Confirmations table:

+---------+---------------------+-----------+
| 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   |
+---------+---------------------+-----------+

Output:

+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 2       | 0.5               |
| 3       | 0                 |
| 6       | 0                 |
| 7       | 1                 |
+---------+-------------------+

View Reference Solution & Explanation ▼

This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, a `CASE` statement for conditional logic, and the `ROUND()` function to format numerical output.

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