Confirmation Rate | Leetcode 1934 | Crack SQL Interviews in 50 Qs

1. Problem Overview & Status Quo

This chapter covers LeetCode Problem 1934: Confirmation Rate, which is the 14th question in the "Crack SQL Interview in 50 Questions" series. The core objective of this problem is to calculate the confirmation rate for every single user registered in the system, even for users who never requested any confirmation messages.

The Two Core Tables

1. Signups Table: Tracks user registrations.
   • user_id (int): Primary Key (contains unique values).
   • time_stamp (datetime): The exact time the user signed up.
2. Confirmations Table: Tracks confirmation messages requested by users.
   • user_id & time_stamp (int & datetime): Composite Primary Key (user_id acts as a Foreign Key referencing the Signups table).
   • action (enum): Can be either 'confirmed' or 'timeout'. Each row indicates that a confirmation message was requested at a specific timestamp and resulted in either a success (confirmed) or an expiration (timeout).

2. Core Mental Model & The Postal/Service Desk Analogy

Real-Life Analogy

Real-Life Analogy (The Service Desk & Account Activation): Imagine you run a digital service where every new user receives an account verification code (like an SMS confirmation or email verification).

• The Signups table is your master customer registry. It lists every person who ever registered an account with you (e.g., Alice, Bob, Charlie, David).
• The Confirmations table is your SMS gateway log. It only contains records for users who actually triggered a verification request and whether they successfully clicked it (confirmed) or let it expire (timeout).

If Charlie signed up for an account but never even checked his phone or requested a confirmation code, his name won't appear inside the SMS gateway log (Confirmations table at all). If you want to generate a company-wide report showing all users and their confirmation success rates, simply inner-joining the two tables would completely erase Charlie! To fix this, you must retain the entire master registry (Signups) and attach whatever matching logs exist (Confirmations), yielding a default score of 0 for anyone who never requested a code.

3. Mathematical Formula & Requirements

The problem statement defines the mathematical formula for the confirmation rate:

Key Edge Cases & Constraints:

• Zero Requests: If a user did not request any confirmation messages, their confirmation rate is defined as 0 (or 0.00).
• Rounding: The final confirmation rate must be rounded up to two decimal places (e.g., 0.50, 0.33, 1.00).
• Result Ordering: The result can be returned in any order.

4. Step-by-Step Example Walkthrough

Let's examine how rows correlate between the two tables to compute the final output.

5. Naive Approach vs. Production-Ready SQL Design

Why an Inner Join Fails

If we write a naive query using an INNER JOIN:

-- NAIVE APPROACH (DO NOT USE)
SELECT s.user_id, c.action
FROM Signups s
JOIN Confirmations c ON s.user_id = c.user_id;

The Flaw: User 6 does not exist in the Confirmations table. An INNER JOIN drops any rows that do not have a match in both tables. Consequently, user 6 vanishes entirely from the final report, violating the requirement that every user must be included.

The Correct Strategy: Left Join + Aggregation + Conditional Summation

To ensure we preserve all users, we must use a LEFT JOIN where the Signups table is on the left side.

6. Building the SQL Query Step-by-Step

Step 1: Performing the Left Join

We start by selecting the user IDs from the Signups table and linking the Confirmations table.

SELECT s.user_id, c.action
FROM Signups s
LEFT JOIN Confirmations c 
  ON s.user_id = c.user_id;

Step 2: Grouping by User ID

Since a single user can have multiple confirmation rows (e.g., user 7 appears three times), we must collapse these rows using GROUP BY.

SELECT s.user_id
FROM Signups s
LEFT JOIN Confirmations c 
  ON s.user_id = c.user_id
GROUP BY s.user_id;

Step 3: Calculating the Confirmation Rate

To calculate the numerator (how many times the action was 'confirmed') and the denominator (total requests), we use aggregate functions:

• SUM(c.action = 'confirmed'): Evaluates the condition row by row. In SQL dialects like MySQL, a boolean condition evaluates to 1 when true and 0 when false, effectively summing up all successful confirmations.
• COUNT(*) or COUNT(c.user_id): Counts the total number of rows associated with that user in the Confirmations table.

SUM(c.action = 'confirmed') / COUNT(*)

Step 4: Rounding and Handling Nulls (IFNULL)

For users with zero requests (like user 6), the left join produces NULL values for columns from the Confirmations table.

• Dividing by COUNT(*) when rows are NULL can result in division errors or NULL outputs.
• We wrap our calculation inside the IFNULL(..., 0.00) function to catch NULL results and convert them to 0.00.
• We wrap the entire division expression in the ROUND(..., 2) function to satisfy the two-decimal-place formatting constraint.

7. Complete Production-Ready SQL Solution

Here is the final, optimized SQL query accepted by LeetCode:

SELECT 
    s.user_id,
    ROUND(
        IFNULL(
            SUM(c.action = 'confirmed') / COUNT(c.user_id), 
            0
        ), 
        2
    ) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c 
    ON s.user_id = c.user_id
GROUP BY s.user_id;

8. Summary and Key Takeaways

• Preserving Unmatched Records: Always use a LEFT JOIN when you need to retain all rows from the primary entity table (e.g., Signups), even if matching child records do not exist in secondary tables.
• Handling Missing Data with IFNULL: Users with zero matching records in secondary tables yield NULL aggregates; use IFNULL(expression, 0) or COALESCE to gracefully substitute default values like 0.00.
• Conditional Summation in SQL: Expressions like SUM(column = 'value') leverage implicit type conversion in MySQL to count exact matches efficiently without complex CASE WHEN blocks.
• Denominator Calculation: COUNT(*) or COUNT(column_name) correctly determines the total sample size (total requests) per group.
• Precision Formatting: Use the ROUND(value, decimal_places) function to format floating-point ratios to the exact decimal requirement specified by interviewers.
• Aggregation Necessity: Whenever you combine individual row attributes with aggregate metrics (SUM, COUNT), a corresponding GROUP BY clause on the primary identifier (user_id) is mandatory.