Subscription Pause Logic
Hulu, Disney+
Problem Statement
Subscription services like Hulu allow users to pause their membership rather than canceling. Product managers want to understand if users are following the default 30-day pause recommendation.
Write a query to count the number of unique users who paused their subscription and resumed it exactly 30 days later.
Rules:
- A pause event is labeled as Pause in the event_type column.
- A resume event is labeled as Resume in the event_type column.
- Exactly 30 days means the difference between the resume_date and pause_date is precisely 30 days (e.g., Paused on 2026-01-01, Resumed on 2026-01-31).
- Output columns: exact_30_day_resumers_count.
- Only one row should be returned with the total count.
Tests your understanding of
Self Joins, Date Arithmetic and Filtering
Input Tables
subscription_history
| history_id(INTEGER) | user_id(INTEGER) | event_type(VARCHAR) | event_date(DATE) |
|---|---|---|---|
| 1 | 1 | Pause | 2026-01-01 |
| 2 | 1 | Resume | 2026-01-31 |
| 3 | 2 | Pause | 2026-01-05 |
| 4 | 2 | Resume | 2026-02-10 |
| 5 | 3 | Pause | 2026-02-01 |
| 6 | 3 | Resume | 2026-03-03 |
| 7 | 4 | Pause | 2026-01-10 |
| 8 | 4 | Resume | 2026-02-09 |
| 9 | 5 | Pause | 2026-03-01 |
| 10 | 5 | Resume | 2026-03-31 |
Expected Output
| exact_30_day_resumers_count(INTEGER) |
|---|
| 4 |
Tags
HardSelf JoinsDate ArithmeticFiltering
25-35 min
31%