Signup Confirmation Success Rate by User

Medium

A product security pipeline tracks whether users complete verification prompts after account registration. Table: Signups user_id: unique identifier for a registered user time_stamp: signup timestamp Table: Confirmations user_id: user who requested a confirmation message time_stamp: confirmation-request timestamp action: either "confirmed" or "timeout" Business metric: For each user, confirmation_rate is the fraction of confirmation requests that ended with action = "confirmed". If a user has zero confirmation requests, confirmation_rate is defined as 0.00. Task: Return each user_id from Signups with their confirmation_rate rounded to two decimal places. Output requirements: Return exactly two columns: user_id confirmation_rate Return one row per user in Signups. Output row order can be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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":2,"confirmation_rate":0.5},
  {"user_id":3,"confirmation_rate":0.0},
  {"user_id":6,"confirmation_rate":0.0},
  {"user_id":7,"confirmation_rate":1.0}
]

Explanation

User 7 confirms every request, user 3 confirms none, user 2 confirms 1 of 2 requests, and user 6 has no requests so their rate is 0.00.

Example 2

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 10      | 2023-01-01 09:00:00  |
| 11      | 2023-01-01 09:05:00  |
| 12      | 2023-01-01 09:10:00  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 10      | 2023-01-02 10:00:00  | confirmed |
| 10      | 2023-01-02 10:01:00  | timeout   |
| 10      | 2023-01-02 10:02:00  | timeout   |
| 10      | 2023-01-02 10:03:00  | timeout   |
| 10      | 2023-01-02 10:04:00  | timeout   |
| 10      | 2023-01-02 10:05:00  | timeout   |
| 10      | 2023-01-02 10:06:00  | timeout   |
| 10      | 2023-01-02 10:07:00  | timeout   |
| 11      | 2023-01-03 10:00:00  | confirmed |
| 11      | 2023-01-03 10:01:00  | confirmed |
| ...     | ...                  | ...       |

Output

[
  {"user_id":10,"confirmation_rate":0.13},
  {"user_id":11,"confirmation_rate":0.23},
  {"user_id":12,"confirmation_rate":0.0}
]

Explanation

User 10 has 1/8 = 0.125, rounded to 0.13. User 11 has 9/40 = 0.225, rounded to 0.23. User 12 has no requests and therefore 0.00.

Example 3

Input

Signups:
| user_id | time_stamp           |
|---------|----------------------|
| 21      | 2024-05-10 08:00:00  |
| 22      | 2024-05-10 08:05:00  |
| 23      | 2024-05-10 08:10:00  |

Confirmations:
| user_id | time_stamp           | action    |
|---------|----------------------|-----------|
| 21      | 2024-05-11 09:00:00  | timeout   |
| 22      | 2024-05-11 09:05:00  | confirmed |
| 22      | 2024-05-11 09:06:00  | confirmed |
| 23      | 2024-05-11 09:07:00  | timeout   |
| 23      | 2024-05-11 09:08:00  | confirmed |

Output

[
  {"user_id":21,"confirmation_rate":0.0},
  {"user_id":22,"confirmation_rate":1.0},
  {"user_id":23,"confirmation_rate":0.5}
]

Explanation

Each user gets an independent denominator based on their own request count.

Constraints

0 <= number of rows in Signups <= 250000
0 <= number of rows in Confirmations <= 400000
Signups.user_id values are unique
(Confirmations.user_id, Confirmations.time_stamp) pairs are unique
Confirmations.user_id references a user in Signups
Confirmations.action is either confirmed or timeout
0 <= user_id <= 1000000000
Return exactly two columns: user_id, confirmation_rate
Round confirmation_rate to exactly 2 decimal places
If a user has no confirmation rows, confirmation_rate must be 0.00
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns user_id and confirmation_rate

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 |

Signups: | user_id | time_stamp | |---------|----------------------| | 10 | 2023-01-01 09:00:00 | | 11 | 2023-01-01 09:05:00 | | 12 | 2023-01-01 09:10:00 | Confirmations: | user_id | time_stamp | action | |---------|----------------------|-----------| | 10 | 2023-01-02 10:00:00 | confirmed | | 10 | 2023-01-02 10:01:00 | timeout | | 10 | 2023-01-02 10:02:00 | timeout | | 10 | 2023-01-02 10:03:00 | timeout | | 10 | 2023-01-02 10:04:00 | timeout | | 10 | 2023-01-02 10:05:00 | timeout | | 10 | 2023-01-02 10:06:00 | timeout | | 10 | 2023-01-02 10:07:00 | timeout | | 11 | 2023-01-03 10:00:00 | confirmed | | 11 | 2023-01-03 10:01:00 | confirmed | | ... | ... | ... |

Signups: | user_id | time_stamp | |---------|----------------------| | 21 | 2024-05-10 08:00:00 | | 22 | 2024-05-10 08:05:00 | | 23 | 2024-05-10 08:10:00 | Confirmations: | user_id | time_stamp | action | |---------|----------------------|-----------| | 21 | 2024-05-11 09:00:00 | timeout | | 22 | 2024-05-11 09:05:00 | confirmed | | 22 | 2024-05-11 09:06:00 | confirmed | | 23 | 2024-05-11 09:07:00 | timeout | | 23 | 2024-05-11 09:08:00 | confirmed |