Coding questions · Confluent
Determine Tutorial Completion Rate by Variant in 7 Days
This question evaluates a candidate's competency in data manipulation and event-based analytics, focusing on skills such as joining user and event tables, aggregating event data, and computing cohort completion metrics by variant.
users
| user_id | sign_up_at | tutorial_variant |
| 101 | 2023-08-01 | original |
| 102 | 2023-08-02 | variant |
| 103 | 2023-08-02 | original |
| 104 | 2023-08-03 | variant |
| 105 | 2023-08-03 | original |
tutorial_events
| event_id | user_id | step_index | event_time |
| 1 | 101 | 1 | 2023-08-01 10:00:00 |
| 2 | 101 | 2 | 2023-08-01 10:02:00 |
| 3 | 102 | 1 | 2023-08-02 11:00:00 |
| 4 | 104 | 1 | 2023-08-03 09:00:00 |
| 5 | 105 | 1 | 2023-08-03 12:00:00 |
Scenario
Confluent wants to understand the impact of two onboarding-tutorial variants (original vs variant) on tutorial completion.
Question
Given the tables below, write a SQL query that returns, for each tutorial_variant, the share of signed-up users who reach the maximum step_index (i.e., complete the tutorial) within 7 days of their sign_up_at date.
Hints
Join users with tutorial_events, find each user’s max step, compare to global max per variant, filter by 7-day window, then aggregate.
Tutorial completion rate by variant within 7 days of signup
Confluent wants to understand the impact of two onboarding-tutorial variants (original vs variant) on tutorial completion. Given the tables users and tutorial_events, write a SQL query that returns, for each tutorial_variant, the share of signed-up users who reach that variant’s maximum tutorial step (maximum step_index observed for that variant) within 7 days of their sign_up_at date. The output should have one row per tutorial_variant with columns tutorial_variant and completion_rate (completed users within 7 days divided by total signed-up users for that variant).
Table users
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | PRIMARY KEY, NOT NULL |
| sign_up_at | DATE | NOT NULL |
| tutorial_variant | VARCHAR(20) | NOT NULL |
Sample data:
| sign_up_at | tutorial_variant | user_id |
|---|---|---|
| 2023-08-01 | original | 101 |
| 2023-08-02 | variant | 102 |
| 2023-08-02 | original | 103 |
| 2023-08-03 | variant | 104 |
| 2023-08-03 | original | 105 |
Table tutorial_events
| column | type | constraints |
|---|---|---|
| event_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES users(user_id) |
| step_index | INTEGER | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
Sample data:
| event_id | event_time | step_index | user_id |
|---|---|---|---|
| 1 | 2023-08-01 10:00:00 | 1 | 101 |
| 2 | 2023-08-01 10:02:00 | 2 | 101 |
| 3 | 2023-08-02 11:00:00 | 1 | 102 |
| 4 | 2023-08-03 09:00:00 | 1 | 104 |
| 5 | 2023-08-03 12:00:00 | 1 | 105 |
Expected output
One row per tutorial_variant with the completion_rate (completed users within 7 days divided by total signed-up users for that variant). In this data, the original variant has a 2-step tutorial (max step_index = 2) and the variant tutorial has a 1-step tutorial (max step_index = 1). Only user 101 completes the original tutorial, and both users 102 and 104 complete the variant tutorial.
| completion_rate | tutorial_variant |
|---|---|
| 0.3333 | original |
| 1 | variant |
Hints
- Join users to tutorial_events and restrict events to the 7-day window from each user’s sign_up_at.
- Compute each user’s maximum step_index within that 7-day window.
- Compute the global maximum step_index per tutorial_variant across all its users.
- Mark a user complete if their 7-day max equals their variant’s global max, then aggregate by variant to get the share.