Label Accounts Active, Dormant or Churned — SQL Problem
For the re-engagement campaign, label every account by how long ago it was last active, as of 2025-06-30.
- Difficulty: Easy
- Topics: Conditional Logic, Dates
- Dialect: MySQL
- Problem: #429
Problem statement
For the re-engagement campaign, label every account by how long ago it was last active, as of 2025-06-30. Let gap be the number of days from last_active_on to 2025-06-30.
active—gapis 0 to 7 days;dormant—gapis 8 to 90 days;churned—gapis more than 90 days;never_active—last_active_onis NULL (signed up but never opened the app).
Return the columns user_id, handle and status, in any order.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| handle | varchar |
| signed_up_on | date |
| last_active_on | date |
Primary key: user_id.
last_active_on is the last day the user opened the app (never after 2025-06-30), or NULL.
Examples
Example 1
users
| user_id | handle | signed_up_on | last_active_on |
|---|---|---|---|
| 1 | aditi.reads | 2024-01-10 | 2025-06-30 |
| 2 | vikram_runs | 2024-03-02 | 2025-06-23 |
| 3 | saanvi.art | 2024-05-18 | 2025-06-22 |
| 4 | karan.k | 2023-11-30 | 2025-04-01 |
| 5 | tanvi_eats | 2023-08-12 | 2025-03-31 |
| 6 | liam.codes | 2025-06-01 | NULL |
Output
| user_id | handle | status |
|---|---|---|
| 1 | aditi.reads | active |
| 2 | vikram_runs | active |
| 3 | saanvi.art | dormant |
| 4 | karan.k | dormant |
| 5 | tanvi_eats | churned |
| 6 | liam.codes | never_active |
How to solve Label Accounts Active, Dormant or Churned
One searched CASE turns the gap into a label. Because CASE returns the first matching branch, the bands can be written as upper bounds only — <= 7 then <= 90 then ELSE — and each branch implicitly means "and not any earlier one".
The NULL branch must come first. DATEDIFF('2025-06-30', NULL) is NULL, every comparison with it is unknown, and without an explicit IS NULL test the row would fall through to ELSE and be labelled churned.
The boundaries are where most mistakes happen: a gap of exactly 7 is still active and exactly 90 still dormant. An equivalent way is to compare the date with cut-off dates computed once, DATE_SUB('2025-06-30', INTERVAL 7 DAY) and INTERVAL 90 DAY, which keeps the column bare. The query is a single scan with no joins.
Reference solution (MySQL)
SELECT user_id, handle,
CASE
WHEN last_active_on IS NULL THEN 'never_active'
WHEN DATEDIFF('2025-06-30', last_active_on) <= 7 THEN 'active'
WHEN DATEDIFF('2025-06-30', last_active_on) <= 90 THEN 'dormant'
ELSE 'churned'
END AS status
FROM usersAnother way
SELECT user_id, handle, IF(last_active_on IS NULL, 'never_active', IF(last_active_on >= DATE_SUB('2025-06-30', INTERVAL 7 DAY), 'active', IF(last_active_on >= DATE_SUB('2025-06-30', INTERVAL 90 DAY), 'dormant', 'churned'))) AS status FROM usersAnother way
SELECT user_id, handle, CASE WHEN last_active_on IS NULL THEN 'never_active' WHEN DATEDIFF('2025-06-30', last_active_on) > 90 THEN 'churned' WHEN DATEDIFF('2025-06-30', last_active_on) > 7 THEN 'dormant' ELSE 'active' END AS status FROM users← Moderation Reports Filed on Weekends in March 2025 · Posts With More Likes Than the Platform Average →