Fct User Activation

1-- Per-user activation flag + activated_at, driven by dim_activation_criteria.
2-- activated_at is the timestamp of the threshold-crossing (min_count-th) qualifying action within the
3-- window — the moment the user actually activated — not their first action.
4{{ config(materialized='table') }}
5
6with criteria as (
7    select action_event, min_count, window_days from {{ ref('dim_activation_criteria') }}
8),
9first_seen as (
10    select person_id, min(event_at) as signup_at
11    from {{ ref('stg_events') }}
12    group by person_id
13),
14qualifying as (
15    -- key actions inside the early window, ranked per person by time
16    select
17        e.person_id,
18        e.event_at,
19        row_number() over (partition by e.person_id order by e.event_at) as rn
20    from {{ ref('stg_events') }} e
21    join first_seen f using (person_id)
22    cross join criteria c
23    where e.event_name = c.action_event
24      and e.event_at <= f.signup_at + (c.window_days || ' day')::interval
25),
26crossed as (
27    -- the min_count-th qualifying action = the activation moment
28    select q.person_id, q.event_at as activated_at
29    from qualifying q
30    cross join criteria c
31    where q.rn = c.min_count
32)
33select
34    f.person_id,
35    f.signup_at,
36    x.person_id is not null as is_activated,
37    x.activated_at
38from first_seen f
39left join crossed x using (person_id)