Monthly Subscription Four-State Breakdown

SQLHardSenior level~15 min

Problem

**[Asked at multiple subscription companies]**

For **February 2023**, classify users by subscription state:
- `new`: first ever subscription this month
- `renewed`: active this month AND previous month
- `reactivated`: active this month, NOT previous month, but had earlier subs
- `churned`: active previous month but NOT this month

Return `state` and `user_count`, ordered by state.

Tables

Example rows — the live problem includes the full dataset.

subscriptions

sub_id	user_id	start_date	end_date

Expected output

Your answer should return 4 rows with the columns state, user_count.

Starter code (SQL)

SELECT *
FROM subscriptions;

Solve this SQL question free

Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

Solution & explanation

Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.