Confirmation Rate
Preview mode. Log in to edit, run, submit, and save progress.
Confirmation Rate
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.
Examples
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.