Confirmation Rate

Confirmation Rate

What is this problem about?

The Confirmation Rate interview question is a SQL problem involving two tables: Signups (user signup timestamps) and Confirmations (logs of confirmation requests, either 'confirmed' or 'timeout'). You need to calculate the confirmation rate for each user, which is the number of 'confirmed' messages divided by the total number of confirmation requests. Users with no requests should have a rate of 0.00.

Why is this asked in interviews?

Companies like Microsoft and Meta ask this Database interview pattern to evaluate your ability to perform aggregations and handle null values. It specifically tests your knowledge of LEFT JOIN, COUNT, SUM/CASE, and rounding functions. It’s a practical data analysis task that mirrors real-world metrics reporting.

Algorithmic pattern used

The query uses a LEFT JOIN and Aggregation with conditional logic.

1. Join Signups and Confirmations on user_id.
2. Group by user_id.
3. To count confirmed messages: SUM(CASE WHEN action = 'confirmed' THEN 1 ELSE 0 END).
4. Divide by COUNT(*) or a count of request rows.
5. Use ROUND to format the result to 2 decimal places and IFNULL or COALESCE to handle users with no requests.

Example explanation

Signups: [1, 2, 3] Confirmations:

• User 1: confirmed, timeout (Rate: 1/2 = 0.50)
• User 2: timeout (Rate: 0/1 = 0.00)
• User 3: (No requests) (Rate: 0.00)

The SQL will join these, group by user, and calculate the ratios. For User 3, the join will result in nulls, which your IFNULL logic should convert to 0.

Common mistakes candidates make

• Using INNER JOIN: Excluding users who haven't made any confirmation requests.
• Integer Division: Performing the division in a way that truncates the decimal (e.g., 1 / 2 = 0). You should multiply by 1.0 or use a float-friendly function.
• Null handling: Forgetting that COUNT(column) ignores nulls, while COUNT(*) counts rows.

Interview preparation tip

Master the CASE WHEN statement in SQL. It's the most versatile tool for creating "virtual columns" based on conditions, which is essential for calculating complex rates and ratios.