Report Confirmation Rates for Every Signup

### Problem Return user_id and confirmation_rate for each Signups user. The rate is confirmed requests divided by all confirmation requests, rounded to two decimals. A user with no requests has rate 0. Include timeout requests in the denominator; any output row order is allowed. ### Tables Table: `Signups` ```text +----------------+----------+ | 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` ```text +----------------+----------+ | 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'). ``` ### Example ```text Signups (user_id,time_stamp): (1,2021-01-01 00:00:00), (2,2021-01-02 00:00:00), (3,2021-01-03 00:00:00) Confirmations (user_id,time_stamp,action): (1,2021-02-01 00:00:00,confirmed), (1,2021-02-02 00:00:00,timeout), (2,2021-02-03 00:00:00,timeout) Output (user_id,confirmation_rate): (1,0.50), (2,0.00), (3,0.00) ``` ### What a Strong Answer Covers - Include every signup and count all confirmation requests in the denominator. - Round the confirmation rate and return zero when a user has no requests. ### Follow-up Questions - How would you calculate the rate using only requests within 24 hours of signup? - How would you report the average confirmation rate across signup months?