-- model: churn_daily · materialized: incremental · unique_key: month
with accounts as (
select account_id, account_status, plan_id
from analytics.accounts_dim
),
events as (
select account_id, event_ts
from analytics.subscription_events
),
joined as (
select a.account_id, a.account_status, e.event_ts
from accounts a
left join events e on e.account_id = a.account_idUsed in EB-TD-03144, Step 3
),
active_accounts as (
select date_trunc('month', event_ts) as month,
count(distinct account_id) as active_start
from joined
where account_status != 'paused'Used in EB-TD-03144, Step 1
and account_status != 'trial'
group by 1
)
select month,
churned::float / nullif(active_start, 0) as churn_rate
from active_accounts