Handle repeated churn in SQL
OpenAIData Scientist · Technical Screen
Last updated
This problem tests your ability to handle complex temporal data by identifying user churn and re-subscription events, then calculating metrics like tenure and churn count per user.
Problem Description
You are provided with a table subscriptions that tracks a user's subscription history. A user can subscribe and cancel multiple times.
Table: subscriptions
| Column | Type | Description |
|---|---|---|
user_id | INT | Unique identifier for the user |
event_type | VARCHAR | 'start' or 'end' |
event_date | DATE | Date the event occurred |
Task
Write a query or Python script to:
- Identify every unique "subscription period" (the interval between a 'start' and the subsequent 'end'). If a user starts a subscription but has not yet ended it, the end date is considered
NULL. - Count how many times each user has "churned" (i.e., completed at least one full subscription period).
- Calculate the average duration of a completed subscription period for each user.
Constraints
event_dateis unique for a specific user within the table.- A user cannot have two 'start' events in a row; events always alternate between 'start' and 'end'.
- Handle users who have started but never ended a subscription.
Example
Input:
| user_id | event_type | event_date |
|---|---|---|
| 1 | start | 2023-01-01 |
| 1 | end | 2023-03-01 |
| 1 | start | 2023-05-01 |
| 2 | start | 2023-01-01 |
Output:
| user_id | churn_count | avg_duration_days |
|---|---|---|
| 1 | 1 | 59.0 |
| 2 | 0 | NULL |
Note: User 1 churned once (Jan to March). The second period (May onwards) is ongoing and thus not included in the churn count or average duration calculation.
Hints
Hint 1
Use a window function like `LEAD()` or `LAG()` to align each 'start' event with its corresponding 'end' event.
Hint 2
Filter out pairs where the 'end' date is NULL before calculating the average duration.
Hint 3
A `GROUP BY` on `user_id` will allow you to aggregate the churn count and average duration using standard aggregate functions.
Follow-up questions
- How would you modify your query to identify 'serial churners'—users who have churned 3 or more times?
- What changes would be necessary if the 'event_date' column actually contained timestamps (including hours and minutes)?
- How would you optimize this query if the `subscriptions` table contained billions of rows?
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.