User Re-Activation Month
Spotify
Problem Statement
Spotify tracks user engagement to measure the effectiveness of "win-back" campaigns. A key metric is identifying re-activated users.
A user is considered re-activated if they have a Premium event after a period of total inactivity lasting 6 months or longer. Inactivity is defined as the gap between any two consecutive events for a specific user.
Rules:
- The inactivity gap must be strictly >= 180 days (6 months).
- The event that ends the gap must be of event_type Premium.
- Output columns: user_id, reactivation_date, days_since_last_event.
- reactivation_date is the date of the Premium event.
- If a user has multiple re-activations, show all of them.
- Result must be sorted by reactivation_date ascending, then user_id ascending.
Tests your understanding of
Window Functions, Date Math and User Retention
Input Tables
user_events
| event_id(INTEGER) | user_id(INTEGER) | event_type(VARCHAR) | event_timestamp(TIMESTAMP) |
|---|---|---|---|
| 1 | 10 | Free | 2023-01-01 10:00:00 |
| 2 | 10 | Premium | 2023-08-15 12:00:00 |
| 3 | 11 | Free | 2023-01-01 10:00:00 |
| 4 | 11 | Free | 2023-03-01 10:00:00 |
| 5 | 11 | Premium | 2023-04-01 10:00:00 |
| 6 | 12 | Free | 2023-01-01 10:00:00 |
| 7 | 12 | Premium | 2024-02-01 10:00:00 |
Expected Output
| user_id(INTEGER) | reactivation_date(DATE) | days_since_last_event(INTEGER) |
|---|---|---|
| 10 | 2023-08-15 | 226 |
| 12 | 2024-02-01 | 396 |
Tags
HardWindow FunctionsDate MathUser Retention
35-45 min
27%