Confirmation Rate
Tik Tok
Problem Statement
To evaluate the effectiveness of the multi-factor authentication system, the security team needs to track how often users successfully confirm their sign-up requests.
The confirmation rate of a user is the number of confirmed messages divided by the total number of requested confirmation messages. Users who did not request any confirmation messages have a confirmation rate of 0.
Write an SQL query to find the confirmation rate of each user.
Rules:
- The confirmation rate must be rounded to 2 decimal places.
- Every user from the Signups table must be included in the final report.
- Return the result table containing user_id and confirmation_rate.
- Results must be sorted by confirmation_rate in ascending order.
Tests your understanding of
Basic SQL, LEFT JOIN, CASE WHEN, Rounding and Aggregation
Input Tables
signups
| user_id(INTEGER) | time_stamp(TIMESTAMP) |
|---|---|
| 3 | 2024-03-21 10:10:23 |
| 7 | 2024-03-20 12:35:00 |
| 2 | 2024-03-22 09:00:14 |
| 6 | 2024-03-22 15:45:00 |
confirmations
| user_id(INTEGER) | time_stamp(TIMESTAMP) | action(ENUM('confirmed', 'timeout')) |
|---|---|---|
| 3 | 2024-03-21 10:12:00 | confirmed |
| 7 | 2024-03-20 12:35:10 | confirmed |
| 7 | 2024-03-20 12:35:50 | timeout |
| 2 | 2024-03-22 09:01:00 | confirmed |
| 2 | 2024-03-22 09:02:00 | timeout |
Expected Output
| user_id(INTEGER) | confirmation_rate(FLOAT) |
|---|---|
| 6 | 0 |
| 2 | 0.5 |
| 7 | 0.5 |
| 3 | 1 |
Tags
MediumBasic SQLLEFT JOINCASE WHENRoundingAggregation
15-20 min
54%