Four-State User Lifecycle Classification

SQLHardSenior levelMeta~15 min

Problem

**[Asked at Meta and Snapchat]**

For the target month **February 2023**, classify each user into one of four
lifecycle states:
- `new`: first ever activity is this month
- `returning`: active this month AND last month
- `resurrected`: active this month, NOT last month, but had activity earlier
- `churned`: active last month but NOT this month

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

Tables

Example rows — the live problem includes the full dataset.

activity

activity_id	user_id	activity_date

Expected output

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

Starter code (SQL)

SELECT *
FROM activity;

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.