📋 Problem Description:
You are given two tables:
Signups: Contains sign-up information for each user.Confirmations: Contains requests for confirmation messages by users, with the status of each request.
Your task is to calculate the confirmation rate for each user — i.e., the percentage of confirmation messages that were successfully confirmed.
🧾 Table Schemas:
📝 Signups Table:
sql
+-----------+---------------------+
| user_id | time_stamp |
+-----------+---------------------+
| int | datetime |
+-----------+---------------------+
user_id is the primary key.- Each row represents when a user signed up.
📨 Confirmations Table:
sql
+-----------+---------------------+-----------+
| user_id | time_stamp | action |
+-----------+---------------------+-----------+
| int | datetime | ENUM |
+-----------+---------------------+-----------+
(user_id, time_stamp) is the primary key.user_id references Signups.user_id.action is either 'confirmed' or 'timeout'.
🧠 Objective:
Calculate the confirmation rate per user:
- Defined as
confirmed_count / total_count - Round to 2 decimal places
- If a user has no confirmation records, the rate is 0.00
🧪 Example Input:
Signups Table:
sql
+---------+---------------------+
| 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:
sql
+---------+---------------------+-----------+
| 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 |
+---------+---------------------+-----------+
✅ Expected Output:
sql
+---------+-------------------+
| user_id | confirmation_rate |
+---------+-------------------+
| 6 | 0.00 |
| 3 | 0.00 |
| 7 | 1.00 |
| 2 | 0.50 |
+---------+-------------------+
🧾 SQL Solution:
sql
SELECT
s.user_id,
ROUND(
IFNULL(SUM(c.action = 'confirmed') / COUNT(c.action), 0),
2
) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c
ON s.user_id = c.user_id
GROUP BY s.user_id;
🔍 Explanation:
LEFT JOIN ensures all users from Signups are included (even those with no confirmations).SUM(c.action = 'confirmed') counts confirmed actions (returns 1 for 'confirmed', 0 otherwise).COUNT(c.action) counts all confirmation actions (both confirmed and timeout).IFNULL(..., 0) ensures that if there were no confirmation records, the result is 0.ROUND(..., 2) ensures the confirmation rate is rounded to 2 decimal places.
💡 Alternate Solution (Using CASE):
sql
SELECT
s.user_id,
ROUND(
IFNULL(SUM(CASE WHEN c.action = 'confirmed' THEN 1 ELSE 0 END)
/ COUNT(c.action), 0),
2
) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c
ON s.user_id = c.user_id
GROUP BY s.user_id;
This version does the same thing but uses a CASE WHEN clause for more clarity.