Lifecycle

1-- Lifecycle: classify each person's weekly activity as new / returning / resurrecting, and count dormant.
2-- Compares each active week to the person's previous active week and their first active week in the window.
3-- Note: first_week is the earliest week WITHIN this window, so a user active just before it can show as
4-- "new" at the window's left edge. Extend the window one interval earlier if that boundary matters.
5WITH activity AS (
6    SELECT DISTINCT
7        person_id,
8        toStartOfWeek(timestamp) AS week
9    FROM events
10    WHERE event = 'core_action'
11      AND timestamp >= now() - INTERVAL 12 WEEK
12),
13enriched AS (
14    SELECT
15        person_id,
16        week,
17        min(week) OVER (PARTITION BY person_id)                                        AS first_week,
18        lagInFrame(week) OVER (PARTITION BY person_id ORDER BY week
19                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_week
20    FROM activity
21)
22SELECT
23    week,
24    countIf(week = first_week)                                                          AS new,
25    countIf(week != first_week AND prev_week = week - INTERVAL 1 WEEK)                  AS returning,
26    countIf(week != first_week AND (prev_week IS NULL OR prev_week < week - INTERVAL 1 WEEK)) AS resurrecting
27FROM enriched
28GROUP BY week
29ORDER BY week
30-- Dormant (users active the prior week but not this week) is derived from the gaps; compute it as a
31-- negative series in the consuming view/insight, or with a symmetric self-anti-join if you need it inline.