Confirmation Rate - Practice Coding Problems

Confirmation Rate - Problem

Database Medium

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

Understanding the Visualization

Input Tables

Signups and Confirmations tables

LEFT JOIN

Preserve all users, match confirmations

Output

Confirmation rate per user

Key Takeaway

🎯 Key Insight: Use LEFT JOIN with conditional aggregation to handle users with zero confirmation requests

Asked in

M Meta 28 A Amazon 22 G Google 18

Use LEFT JOIN to preserve all users, then apply conditional aggregation with CASE statements to count confirmed actions versus total requests, handling division by zero for users with no confirmations.

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)

Common Approaches

Approach	Time	Space	Notes
✓ LEFT JOIN with Conditional Aggregation	O(n + m)	O(n)	Join all users with their confirmations and calculate rates using conditional counting

LEFT JOIN with Conditional Aggregation — Algorithm Steps

Step 1: LEFT JOIN Signups with Confirmations to include all users
Step 2: Use conditional aggregation to count confirmed vs total requests
Step 3: Calculate rate with division and handle division by zero

Visualization

Tap to expand

Step-by-Step Walkthrough

LEFT JOIN

Include all users from Signups

Group By

Group by user_id

Calculate

Count confirmed/total ratio

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m)

Linear scan of both tables with hash join

✓ Linear Growth

Space Complexity

O(n)

Space for grouping by user_id

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

LEFT JOIN with Conditional Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler