Identify active subscriptions and sum revenue

1-- core.stripe.mrr_active_subscriptions
2-- Identify active subscriptions and sum revenue. This SQL model fetches the monthly
3-- revenue per subscription and applies discounts to provide a net monthly amount.
4-- It then identifies if a subscription is active or canceled based on the status.
5--
6-- Grain: customer x month a subscription started. status and canceled_at
7-- describe the customer (active if any subscription is still live), so each
8-- customer has one row per start month for the time series to join on.
9--
10-- From the weld.app template library, corrected. Not reconciled against
11-- Stripe; check the output against the source before relying on it.
12
13with
14revenue_per_subscription as (
15    select
16        customer_id,
17        cast(date_trunc(start_date, month) as date) as start_date_month,
18        cast(date_trunc(canceled_at, month) as date) as canceled_at,
19        max(amount_off) as amount_off,
20        status,
21        sum(monthly_amount_eur) as monthly_amount_eur,
22        sum(monthly_amount_eur) - ifnull(max(amount_off), 0) as monthly_amount_eur_after_discount
23    from
24        {{core.stripe.mrr_normalization}}
25    group by
26        customer_id,
27        start_date_month,
28        canceled_at,
29        status
30),
31active_subscriptions as (
32    select
33        customer_id,
34        max(status) as status
35    from
36        revenue_per_subscription
37    where
38        status <> 'canceled'
39    group by
40        customer_id