Confirmation Rate

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given two tables - Signups and Confirmations. The Signups table contains the registration time of each user. The Confirmations table logs each confirmation message request made by a user, along with whether it was 'confirmed' or 'timeout'. The confirmation rate of a user is defined as: confirmation_rate = (number of 'confirmed' actions) / (total number of confirmation requests) If a user never requested a confirmation message, their confirmation rate is 0. Write a SQL query to find the confirmation rate of each user, rounded to 2 decimal places. Return the result in any order.

Database Schema

Signups

Column Name	Example Value
user_id	3
time_stamp	2020-03-21 10:16:13

Confirmations

Column Name	Example Value
user_id	3
time_stamp	2021-01-06 03:30:46
action	timeout

Example

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

Explanation:

User 6 made no requests → rate is 0.00. User 3 made 2 requests, both timed out → 0/2 = 0.00. User 7 made 3 requests, all confirmed → 3/3 = 1.00. User 2 made 2 requests, 1 confirmed → 1/2 = 0.50.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.