Identify active subscriptions and sum revenue

1with
2    revenue_per_subscription as (
3        select
4            customer_id
5          , cast(date_trunc(start_date, month) as date) as start_date_month
6          , cast(date_trunc(canceled_at, month) as date) as canceled_at
7          , max(amount_off) as amount_off
8          , status
9          , sum(monthly_amount_eur) as monthly_amount_eur
10          , sum(monthly_amount_eur) - ifnull(max(amount_off), 0) as monthly_amount_eur_after_discount
11        from
12            {{stripe_demo.stripe_mrr_normalization}}
13        group by
14            customer_id
15          , start_date_month
16          , canceled_at
17          , status
18    )
19  , active_subscriptions as (
20        select
21            customer_id
22          , max(status) as status
23        from
24            revenue_per_subscription
25        where
26            status <> 'canceled'
27        group by
28            customer_id
29    )
30  , cancelled_subscriptions as (
31        select
32            customer_id
33          , max(canceled_at) as canceled_at
34          , max(status) as status
35        from
36            revenue_per_subscription
37        where
38            status = 'canceled'
39        group by
40            customer_id
41    )
42select
43    r.customer_id
44  , coalesce(s.status, c.status) as status
45  , start_date_month
46  , case
47        when coalesce(s.status, c.status) = 'canceled' then c.canceled_at
48        else null
49    end as canceled_at
50  , cast(
51        round(sum(monthly_amount_eur_after_discount), 0) as int
52    ) as monthly_amount_eur_after_discount
53from
54    revenue_per_subscription r
55    left join active_subscriptions s on r.customer_id = s.customer_id
56    left join cancelled_subscriptions c on r.customer_id = c.customer_id
57group by
58    customer_id
59  , status
60  , r.status
61  , start_date_month
62  , canceled_at
63order by
64    customer_id