Resurrected Users — Solution & ExplanationProblems / Resurrected Users / EditorialApproach

Per-event gaps. LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) — every event learns when its user was last seen.

Filter to resurrections. Non-NULL previous time and DATE_DIFF('day', prev, current) > 30, strictly. Keeping the definition at the event level means repeat resurrections by one user all count — which is what a growth team wants from this metric.

Monthly rollup. Truncate the *resurrection event's* timestamp to month and count. Quiet months simply produce no group.

Key InsightResurrection is a property of a transition between consecutive events, and LAG is the transition detector: it turns a time series into (previous, current) pairs you can filter with plain WHERE logic. Notice what stays out of the query — no sessionization, no calendars, no self-joins. When a metric reads as "an X whose relation to the previous X satisfies P", the LAG-then-filter shape is almost always the whole answer.Hints

The unit of analysis is the event, not the user: LAG(event_time) per user gives each event its own gap, and one user can resurrect several times.

First events have NULL previous time. NULL comparisons are unknown (not true), so WHERE would drop them anyway — but write prev_time IS NOT NULL explicitly; relying on NULL-propagation is how off-by-one bugs sneak in later.

The threshold is strict: > 30 days. The data plants a gap of exactly 30 days (Feb 1 to Mar 2 in a leap year) that must NOT count.

The month bucket comes from the resurrection event's own timestamp — not the month the user went quiet.

Solve Resurrected Users yourself →