Subscription Growth Rate
ASKED IN INTERVIEWNetflix, Spotify
Problem Statement
For subscription-based services, tracking growth speed is vital for assessing market health. You are tasked with calculating the Month-over-Month (MoM) growth rate of paid subscribers.
Write an SQL query to calculate the growth rate for each month.
Rules:
- Only consider users with an active status.
- Growth Rate Formula: ((Current Month Count - Previous Month Count) / Previous Month Count) * 100.
- For the first month in the dataset, the growth rate should be NULL as there is no previous month to compare.
- Output columns: month_start_date, current_subscribers, previous_subscribers, mom_growth_percentage.
- Round the mom_growth_percentage to 2 decimal places.
- Results must be returned in ascending order by month_start_date.
Table Schema:
- Subscriptions: sub_id, user_id, start_date (DATE), status (active, cancelled), plan_type.
Tests your understanding of
Window Functions, LAG, Arithmetic and DateTime
Input Tables
Subscriptions
| user_id(INTEGER) | start_date(DATE) | status(VARCHAR) |
|---|---|---|
| 1 | 2026-01-05 | active |
| 2 | 2026-01-12 | active |
| 3 | 2026-01-20 | active |
| 4 | 2026-01-25 | active |
| 5 | 2026-02-02 | active |
| 6 | 2026-02-14 | active |
| 7 | 2026-02-28 | active |
| 8 | 2026-03-05 | active |
| 9 | 2026-03-15 | active |
Expected Output
| month_start_date(DATE) | current_subscribers(INTEGER) | previous_subscribers(INTEGER) | mom_growth_percentage(DECIMAL) |
|---|---|---|---|
| 2026-01-01 | 4 | null | null |
| 2026-02-01 | 3 | 4 | -25 |
| 2026-03-01 | 2 | 3 | -33.33 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsLAGArithmeticDateTime
15-20 min
52%