Skip to main content
Talentd

Confirmation Rate - Solution & Explanation

MediumDatabase9 min readAsked at: Amazon, Microsoft, Meta +2
Practice this problem

Problem Statement

Table: Signups

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
+----------------+----------+
user_id is the column of unique values for this table.
Each row contains information about the signup time for the user with ID user_id.

 

Table: Confirmations

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| user_id        | int      |
| time_stamp     | datetime |
| action         | ENUM     |
+----------------+----------+
(user_id, time_stamp) is the primary key (combination of columns with unique values) for this table.
user_id is a foreign key (reference column) to the Signups table.
action is an ENUM (category) of the type ('confirmed', 'timeout')
Each row of this table indicates that the user with ID user_id requested a confirmation message at time_stamp and that confirmation message was either confirmed ('confirmed') or expired without confirming ('timeout').

 

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Signups table:
+---------+---------------------+
| 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 table:
+---------+---------------------+-----------+
| 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.00              |
| 3       | 0.00              |
| 7       | 1.00              |
| 2       | 0.50              |
+---------+-------------------+
Explanation: 
User 6 did not request any confirmation messages. The confirmation rate is 0.
User 3 made 2 requests and both timed out. The confirmation rate is 0.
User 7 made 3 requests and all were confirmed. The confirmation rate is 1.
User 2 made 2 requests where one was confirmed and the other timed out. The confirmation rate is 1 / 2 = 0.5.

Approach Overview

Problem Overview: You are given two tables: Signups (all registered users) and Confirmations (confirmation requests with actions such as confirmed or timeout). For each user, compute the confirmation rate defined as confirmed_requests / total_requests. Users with no confirmation requests must return a rate of 0.00.

Approach 1: Using SQL Aggregation with Joins (O(n) time, O(1) extra space)

Join the Signups table with Confirmations using a LEFT JOIN so users without confirmation records are still included. Group by user_id and compute the rate using conditional aggregation. A common pattern is AVG(action = 'confirmed'), which treats true values as 1 and false as 0. This directly returns the fraction of confirmed requests. If a user has no rows in Confirmations, wrap the result with COALESCE to return 0. This approach scans the confirmation records once and performs grouping, making it efficient for large datasets.

Approach 2: Using SQL Aggregate Functions (O(n) time, O(1) extra space)

Another SQL strategy explicitly counts confirmed and total requests. After a LEFT JOIN, use SUM(CASE WHEN action='confirmed' THEN 1 ELSE 0 END) for confirmed requests and COUNT(action) for total requests. Divide these values and round to two decimal places using ROUND(). This approach is slightly more verbose but very explicit about how the metric is calculated. It is useful when databases do not support boolean expressions inside aggregate functions.

Approach 3: Data Aggregation Using a Programming Language (O(n) time, O(n) space)

Load both tables into a structure such as dictionaries or hash maps in Python. Iterate through the Confirmations table and track per-user counts: total requests and confirmed requests. After building these counts, iterate through all users in Signups and compute the rate. This method mirrors what the SQL GROUP BY does internally. It works well when processing data outside a database pipeline or when integrating with application logic.

Approach 4: Programmatic Calculation Using Scripting Languages (O(n) time, O(n) space)

Scripting approaches follow the same idea but emphasize step-by-step data processing. Parse confirmation records, accumulate counts using a map keyed by user_id, then calculate the ratio for each signup record. Formatting to two decimal places ensures the output matches database-style reporting. While less concise than SQL, this method gives full control over preprocessing and validation.

Recommended for interviews: The SQL aggregation with LEFT JOIN and GROUP BY is the expected solution. It demonstrates understanding of relational joins, conditional aggregation, and handling missing rows. Knowing both the boolean AVG() trick and the explicit SUM/COUNT version shows strong command of SQL, database queries, and data aggregation patterns.

Approach 1: Using SQL Aggregation with Joins

This approach uses SQL queries to calculate the confirmation rate by joining the Signups and Confirmations tables. The query groups the data by user_id and computes the count of 'confirmed' actions and total actions. Then, the confirmation rate is calculated as the ratio of confirmed actions to total actions, with appropriate handling for users with no confirmation requests.

This solution first aggregates the Confirmations table to compute total requests and confirmed requests for each user. It uses a LEFT JOIN with the Signups table to ensure that all users appear in the result, even if they have no requests. The confirmation rate is computed as the ratio of confirmed requests to total requests, and the result is rounded to two decimal places.

Code

SQL

SQL

Complexity

Time Complexity: O(N + M), where N is the number of records in Signups and M is the number of records in Confirmations.
Space Complexity: O(U), where U is the number of unique users, to store intermediate results.

Try this approach in the editor →

Approach 2: Data Aggregation Using a Programming Language

This approach processes the confirmation data in a procedural language by reading the data into appropriate structures (dictionaries, hashmaps, etc.), and then computing the confirmation rate for each user by iterating through the data. This method allows for additional logic and control beyond SQL functionalities.

This solution uses Python's defaultdict to count confirmations and actions for each user. It iterates over the Confirmations dataset, updating counts based on the action field. Then, it computes the confirmation rate as the ratio of confirmed counts to total counts. Results are stored in a dictionary and printed or returned after sorting.

Code

Python

Python

Complexity

Time Complexity: O(N + M), where N is the number of records in Signups and M is the number of records in Confirmations.
Space Complexity: O(U), where U is the number of unique users to store confirmation stats.

Try this approach in the editor →

Approach 3: Using SQL Aggregate Functions

This approach leverages SQL's grouping and aggregation capabilities to compute the confirmation rate in a straightforward manner.

The idea is to left join the Signups table with the Confirmations table on user_id, then group the results by user_id. For each group, calculate the count of 'confirmed' actions and divide by the total count of actions to get the confirmation rate.

This SQL query performs a LEFT JOIN between the Signups and Confirmations tables based on user_id. It groups the results by user_id and uses conditional aggregation to count confirmed actions and total actions. The COALESCE function is used to return 0 when there are no confirmation requests.

Code

SQL

SQL

Complexity

Time Complexity: O(N), where N is the number of rows in the Confirmations table.
Space Complexity: O(1), as no extra space is used beyond the result set.

Try this approach in the editor →

Approach 4: Programmatic Calculation Using Scripting Languages

This approach uses a programming language to parse the input data, calculate the confirmation rate for each user programmatically, and output the results.

We iterate through the confirmations table, counting 'confirmed' actions and total actions for each user. After populating our counts, we compute the confirmation rate for each user. We initialize users in the Signups table with zero confirmations and total requests if they aren't present in the Confirmations table.

This Python code first initializes signups and confirmations data. It then processes the confirmations to count the number of 'confirmed' actions and the total number of requests per user using a defaultdict. Finally, it iterates over all users from the signups list, computes the confirmation rate if any requests were made, rounds it, and returns the result list.

Code

Python

Python

Complexity

Time Complexity: O(N + M), where N is the number of unique user_ids in Signups and M is the number of rows in the Confirmations table.
Space Complexity: O(U), where U is the number of unique user_ids that made confirmation requests.

Try this approach in the editor →

Approach 5: Left Join + Grouping

We can use a left join to join the Signups table and the Confirmations table on user_id, and then use GROUP BY to group by user_id for aggregation.

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Using SQL Aggregation with Joins

Time Complexity: O(N + M), where N is the number of records in Signups and M is the number of records in Confirmations.
Space Complexity: O(U), where U is the number of unique users, to store intermediate results.

Data Aggregation Using a Programming Language

Time Complexity: O(N + M), where N is the number of records in Signups and M is the number of records in Confirmations.
Space Complexity: O(U), where U is the number of unique users to store confirmation stats.

Using SQL Aggregate Functions

Time Complexity: O(N), where N is the number of rows in the Confirmations table.
Space Complexity: O(1), as no extra space is used beyond the result set.

Programmatic Calculation Using Scripting Languages

Time Complexity: O(N + M), where N is the number of unique user_ids in Signups and M is the number of rows in the Confirmations table.
Space Complexity: O(U), where U is the number of unique user_ids that made confirmation requests.

Left Join + Grouping—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL Aggregation with LEFT JOINO(n)O(1)Best general SQL solution for relational databases
SQL SUM/COUNT Aggregate FunctionsO(n)O(1)When explicit conditional aggregation is preferred
Python Data AggregationO(n)O(n)When processing exported data outside the database
Programmatic Scripting CalculationO(n)O(n)Useful in ETL pipelines or application-level data processing

Video Solution

Confirmation Rate | Leetcode 1934 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 22,970 views views

Watch 9 more video solutions →

Frequently Asked Questions

Confirmation Rate Python solution
A Python solution reads confirmation records and stores counts in a dictionary keyed by user_id. Track total requests and confirmed requests, then iterate through the Signups list to compute confirmed/total for each user. This approach runs in O(n) time with O(n) additional memory.
Is Confirmation Rate easy or hard?
Confirmation Rate is generally considered a medium difficulty SQL problem. The challenge is understanding how to include users with no confirmation rows and correctly compute the ratio using aggregation. Knowledge of LEFT JOIN and conditional aggregation makes the problem straightforward.
How to solve Confirmation Rate in O(n)?
Join Signups with Confirmations using a LEFT JOIN and group by user_id. Compute the ratio of confirmed actions to total requests using AVG(action='confirmed') or SUM(action='confirmed')/COUNT(action). Because each row is processed once during aggregation, the runtime remains O(n).
What is the best approach for Confirmation Rate?
The most efficient approach uses a SQL LEFT JOIN between Signups and Confirmations followed by GROUP BY user_id. The confirmation rate can be calculated using AVG(action = 'confirmed') or a SUM/COUNT combination. This runs in O(n) time over the confirmation records and uses constant extra space.
Is Confirmation Rate asked at Google/Amazon/Meta?
Confirmation Rate represents a classic SQL aggregation and join problem commonly used in data and backend interviews. Similar questions appear in interviews at companies like Amazon, Meta, and data-focused roles where candidates must compute metrics from relational tables.
What data structure is used in Confirmation Rate?
The SQL solution relies on relational database operations such as joins and aggregation rather than explicit data structures. In a Python implementation, a hash map (dictionary) is typically used to track counts of total and confirmed requests per user.
What is the time complexity of Confirmation Rate?
The typical SQL solution runs in O(n) time where n is the number of rows in the Confirmations table. The database performs a join and aggregation scan once. Space complexity is O(1) because aggregation happens within the query engine without storing additional structures.

Ready to solve this problem?

Practice Confirmation Rate with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary