Label Accounts Active, Dormant or Churned

Easy
429.

Label Accounts Active, Dormant or Churned

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 — gap is 0 to 7 days;
  • dormant — gap is 8 to 90 days;
  • churned — gap is more than 90 days;
  • never_active — last_active_on is NULL (signed up but never opened the app).

Return the columns user_id, handle and status, in any order.

Tables

Table: users

ColumnType
user_idint
handlevarchar
signed_up_ondate
last_active_ondate

Primary key: user_id.

last_active_on is the last day the user opened the app (never after 2025-06-30), or NULL.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

users

user_idhandlesigned_up_onlast_active_on
1aditi.reads2024-01-102025-06-30
2vikram_runs2024-03-022025-06-23
3saanvi.art2024-05-182025-06-22
4karan.k2023-11-302025-04-01
5tanvi_eats2023-08-122025-03-31
6liam.codes2025-06-01NULL

Output

user_idhandlestatus
1aditi.readsactive
2vikram_runsactive
3saanvi.artdormant
4karan.kdormant
5tanvi_eatschurned
6liam.codesnever_active
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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 — gap is 0 to 7 days;
  • dormant — gap is 8 to 90 days;
  • churned — gap is more than 90 days;
  • never_active — last_active_on is NULL (signed up but never opened the app).

Return the columns user_id, handle and status, in any order.

Tables

Table: users

ColumnType
user_idint
handlevarchar
signed_up_ondate
last_active_ondate

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_idhandlesigned_up_onlast_active_on
1aditi.reads2024-01-102025-06-30
2vikram_runs2024-03-022025-06-23
3saanvi.art2024-05-182025-06-22
4karan.k2023-11-302025-04-01
5tanvi_eats2023-08-122025-03-31
6liam.codes2025-06-01NULL

Output

user_idhandlestatus
1aditi.readsactive
2vikram_runsactive
3saanvi.artdormant
4karan.kdormant
5tanvi_eatschurned
6liam.codesnever_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 users

Another 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 users

Another 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 →