Fct Lifecycle

1-- Lifecycle: per person-week status (new / returning / resurrecting). Dormant is derived downstream.
2{{ config(materialized='table') }}
3
4with activity as (
5    select distinct
6        person_id,
7        date_trunc('week', event_at) as week
8    from {{ ref('stg_events') }}
9    where event_name = 'core_action'
10),
11enriched as (
12    select
13        person_id,
14        week,
15        min(week) over (partition by person_id) as first_week,
16        lag(week) over (partition by person_id order by week) as prev_week
17    from activity
18)
19select
20    week,
21    count(*) filter (where week = first_week)                                              as new_users,
22    count(*) filter (where week <> first_week and prev_week = week - interval '1 week')     as returning_users,
23    count(*) filter (where week <> first_week and (prev_week is null or prev_week < week - interval '1 week')) as resurrecting_users
24from enriched
25group by week
26order by week