Dormant Users Report — Solution & ExplanationProblems / Dormant Users Report / EditorialApproach

Last activity per user. MAX(event_time) grouped by user, truncated to the day. This is a plain aggregate CTE — the interesting part is what happens to users who aren't in it.

LEFT JOIN from users. Driving from the user dimension keeps never-active users as rows with NULL activity columns; driving from activity can never produce them.

Two-branch filter. la.user_id IS NULL OR days_dormant > 14. The IS NULL branch is not decoration: NULL > 14 is *unknown* in three-valued logic, so without the explicit branch the never-active — the most dormant users of all — silently drop out.

Output the NULL-preserving columns ordered by user_id.

Key Insight"Inactive" reports ask about entities defined by the absence of facts, and three-valued logic is where they break: every comparison against NULL is unknown, and WHERE discards unknowns. The pattern to internalize — LEFT JOIN from the dimension, then filter with an explicit IS NULL OR <condition> — keeps both flavors of dormancy (long-ago and never) in one honest query.Hints

Start from users, not activity — never-active users have no activity rows, and only a LEFT JOIN from the user list can produce them.

The dormancy filter needs an explicit IS NULL branch: for never-active users days_dormant is NULL, and NULL > 14 evaluates to unknown, which WHERE treats as false.

Truncate the MAX(event_time) to a day before differencing — the spec defines days_dormant between the activity DATE and the reference date, so an evening event must not shave a day off.

Check the boundary: exactly 14 days dormant stays OFF the report (the condition is strictly greater), and the data includes a user parked right on it.

Solve Dormant Users Report yourself →