Fct Retention

1-- Weekly retention matrix (recurring). Uses dbt's cross-database datediff macro for the interval count;
2-- date_trunc still varies by warehouse, so adjust it if yours differs.
3{{ config(materialized='table') }}
4
5with activity as (
6    select distinct
7        person_id,
8        date_trunc('week', event_at) as week
9    from {{ ref('stg_events') }}
10    where event_name = 'core_action'
11),
12cohort as (
13    select person_id, min(week) as cohort_week
14    from activity
15    group by person_id
16),
17joined as (
18    select
19        c.cohort_week,
20        a.person_id,
21        {{ dbt.datediff('c.cohort_week', 'a.week', 'week') }} as weeks_later
22    from cohort c
23    join activity a using (person_id)
24    where a.week >= c.cohort_week
25)
26select
27    cohort_week,
28    weeks_later,
29    count(distinct person_id) as retained,
30    count(distinct person_id)::numeric
31        / max(count(distinct person_id)) over (partition by cohort_week) as retention_rate
32from joined
33group by cohort_week, weeks_later