Confirmation Rate - Practice Coding Problems

Confirmation Rate - Problem

Database Medium

You're building a user engagement analytics system for a mobile app that sends confirmation messages to users. Your task is to calculate the confirmation rate for each user based on their interaction history.

You have two tables:

Signups: Contains user registration data with user_id and time_stamp
Confirmations: Contains confirmation message attempts with user_id, time_stamp, and action (either 'confirmed' or 'timeout')

The confirmation rate is calculated as: Number of 'confirmed' messages ÷ Total confirmation requests

Special Rules:

Users who never requested confirmations have a rate of 0.00
Round the result to 2 decimal places
Include all users from the Signups table in your results

Input & Output

basic_case.sql — Basic Example

$ Input: Signups: [(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: [(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: [(2, 0.50), (3, 0.00), (6, 0.00), (7, 1.00)]

💡 Note: User 2: 1 confirmed out of 2 total = 0.50. User 3: 0 confirmed out of 2 total = 0.00. User 6: No confirmations = 0.00. User 7: 3 confirmed out of 3 total = 1.00.

single_user.sql — Single User

$ Input: Signups: [(1, '2020-01-01 10:00:00')] Confirmations: [(1, '2020-01-02 11:00:00', 'confirmed')]

› Output: [(1, 1.00)]

💡 Note: User 1 has 1 confirmed message out of 1 total, resulting in a perfect confirmation rate of 1.00.

no_confirmations.sql — Edge Case

$ Input: Signups: [(1, '2020-01-01 10:00:00'), (2, '2020-01-02 10:00:00')] Confirmations: []

› Output: [(1, 0.00), (2, 0.00)]

💡 Note: Both users signed up but never requested any confirmation messages, so their confirmation rates are 0.00.

Constraints

1 ≤ signups.length ≤ 10⁴
0 ≤ confirmations.length ≤ 10⁴
Each user_id in Confirmations exists in Signups
Confirmation rates must be rounded to exactly 2 decimal places
Action values are only 'confirmed' or 'timeout'

Visualization

Tap to expand

Understanding the Visualization

LEFT JOIN Tables

Connect every signup with their confirmation attempts (if any)

Group By User

Aggregate all confirmation data for each user

Count & Calculate

Count confirmed vs total, compute rate with proper null handling

Format Result

Round to 2 decimal places and return user_id with rate

Key Takeaway

🎯 Key Insight: LEFT JOIN preserves all users while conditional aggregation efficiently calculates rates in a single query pass

Asked in

f Meta 45 a Amazon 38 G Google 32 ⊞ Microsoft 28

The optimal solution uses a LEFT JOIN between Signups and Confirmations tables with GROUP BY aggregation. Key techniques: SUM(CASE...) for conditional counting, COALESCE to handle users with no confirmations, and NULLIF to prevent division by zero. This approach achieves O(n+m) time complexity with a single pass through both tables.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(1)	Use separate subqueries for each user to count confirmations
LEFT JOIN with Aggregation (Optimal)	O(n + m)	O(n)	Single query using LEFT JOIN and conditional aggregation

Brute Force (Multiple Subqueries) — Algorithm Steps

Select all users from Signups table
For each user, count total confirmation requests with a subquery
For each user, count confirmed messages with another subquery
Calculate rate using CASE statement to handle division by zero
Round result to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Get Users

Select all users from Signups table

Count Total

For each user, subquery counts all their confirmations

Count Confirmed

Another subquery counts only 'confirmed' actions

Calculate Rate

Divide confirmed by total, handle zero division

Code -

solution.c — C

// SQL solution using multiple subqueries
char query[] = "
    SELECT 
        s.user_id,
        ROUND(
            CASE 
                WHEN (SELECT COUNT(*) FROM Confirmations c1 WHERE c1.user_id = s.user_id) = 0 
                THEN 0.00
                ELSE 
                    (SELECT COUNT(*) FROM Confirmations c2 WHERE c2.user_id = s.user_id AND c2.action = 'confirmed') * 1.0 /
                    (SELECT COUNT(*) FROM Confirmations c3 WHERE c3.user_id = s.user_id)
            END, 2
        ) as confirmation_rate
    FROM Signups s
    ORDER BY s.user_id;
";

// Execute using database API

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n users, we scan through all m confirmation records twice

⚠ Quadratic Growth

Space Complexity

O(1)

Only temporary variables for calculations

✓ Linear Space

28.4K Views

Medium Frequency

~15 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler