Confirmation Rate - Problem

You have two tables: Signups and Confirmations.

The Signups table contains information about when users signed up, with columns for user_id and time_stamp.

The Confirmations table tracks confirmation requests, with columns for user_id, time_stamp, and action (either 'confirmed' or 'timeout').

Your task: Calculate the confirmation rate for each user. The confirmation rate is the number of 'confirmed' messages divided by the total number of requested confirmation messages. If a user didn't request any confirmations, their rate is 0. Round the result to two decimal places.

Table Schema

Signups
Column Name Type Description
user_id PK int Unique identifier for each user
time_stamp datetime When the user signed up
Primary Key: user_id
Confirmations
Column Name Type Description
user_id PK int Foreign key referencing Signups.user_id
time_stamp PK datetime When the confirmation was requested
action ENUM Result of confirmation: 'confirmed' or 'timeout'
Primary Key: (user_id, time_stamp)

Input & Output

Example 1 — Mixed Confirmation Rates
Input Tables:
Signups
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
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
6 0
3 0
7 1
2 0.5
💡 Note:

User 6 made no confirmation requests, so rate is 0.00. User 3 had 2 timeouts (0/2 = 0.00). User 7 confirmed all 3 requests (3/3 = 1.00). User 2 confirmed 1 out of 2 requests (1/2 = 0.50).
Example 2 — No Confirmations Requested
Input Tables:
Signups
user_id time_stamp
1 2020-01-01 00:00:00
2 2020-01-02 00:00:00
Confirmations
user_id time_stamp action
Output:
user_id confirmation_rate
1 0
2 0
💡 Note:

Both users signed up but never requested any confirmations. Their confirmation rates are 0.00 as specified in the problem requirements.

Constraints

  • 1 ≤ user_id ≤ 1000
  • action is either 'confirmed' or 'timeout'
  • time_stamp is a valid datetime

Visualization

Tap to expand
Confirmation Rate INPUT Signups Table user_id time_stamp 3 2020-03-21 7 2020-01-04 2 2020-07-29 6 2020-12-09 Confirmations Table user_id time action 3 2021-01-06 confirmed 7 2021-06-12 confirmed 7 2021-06-13 timeout 7 2021-06-14 confirmed 2 2021-01-22 confirmed 2 2021-01-28 timeout ALGORITHM STEPS 1 LEFT JOIN Tables Join Signups with Confirmations ON user_id = user_id 2 GROUP BY user_id Aggregate requests per user 3 Count Confirmations SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0) 4 Calculate Rate confirmed / total requests ROUND(ratio, 2) Formula: confirmed_count total_requests (or 0) FINAL RESULT user_id rate 6 0.00 3 1.00 7 0.67 2 0.50 Calculation Details: User 6: 0/0 = 0.00 (no requests) User 7: 2/3 = 0.67 (2 confirmed) OK - Solution Complete All rates rounded to 2 decimals Key Insight: Use LEFT JOIN to include users with no confirmation requests (rate = 0). IFNULL or COALESCE handles division by zero when total_requests = 0. AVG(action='confirmed') is an elegant alternative to COUNT/SUM ratio calculation. TutorialsPoint - Confirmation Rate | Optimal Solution

Related Problems

Asked in
Meta 28 Amazon 22 Google 18
28.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen