00:00:00

Description

Editorial

Signup Confirmation Success Rate by User

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Signups =

[{"user_id":3,"time_stamp":"2021-01-01 00:00:00"},{"user_id":7,"time_stamp":"2021-01-01 00:01:00"},{"user_id":2,"time_stamp":"2021-01-01 00:02:00"},{"user_id":6,"time_stamp":"2021-01-01 00:03:00"}]

Confirmations =

[{"user_id":3,"time_stamp":"2021-01-02 00:00:00","action":"timeout"},{"user_id":3,"time_stamp":"2021-01-02 00:01:00","action":"timeout"},{"user_id":7,"time_stamp":"2021-01-08 22:40:00","action":"confirmed"},{"user_id":7,"time_stamp":"2021-01-08 22:41:00","action":"confirmed"},{"user_id":7,"time_stamp":"2021-01-08 22:42:00","action":"confirmed"},{"user_id":2,"time_stamp":"2021-01-15 21:20:00","action":"confirmed"},{"user_id":2,"time_stamp":"2021-01-15 21:21:00","action":"timeout"}]

Loading problem...

101

00:00:00

Description

Editorial

Signup Confirmation Success Rate by User

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Signups =

[{"user_id":3,"time_stamp":"2021-01-01 00:00:00"},{"user_id":7,"time_stamp":"2021-01-01 00:01:00"},{"user_id":2,"time_stamp":"2021-01-01 00:02:00"},{"user_id":6,"time_stamp":"2021-01-01 00:03:00"}]

Confirmations =

Signup Confirmation Success Rate by User

Hints

Signup Confirmation Success Rate by User

Hints