Food waste by area month

1-- core.esmiley.food_waste_by_area_month
2-- Monthly food waste per account and top-level area, with each area's share of
3-- the account's waste and the change from the previous month.
4-- Grain: month x account x area x currency.
5--
6-- share_of_account_kg is the area's weight over the account's total weight in the
7-- month. kg_change_vs_previous_month compares with the same area's previous
8-- calendar month and is NULL when that month had no registrations.
9
10WITH monthly AS (
11    SELECT
12        DATE_TRUNC(registration_date, MONTH)   AS month,
13        account_id,
14        ANY_VALUE(account_name)                AS account_name,
15        COALESCE(area, 'Unassigned')           AS area,
16        currency,
17        SUM(registrations)                     AS registrations,
18        SUM(waste_kg)                          AS waste_kg,
19        SUM(cost)                              AS cost,
20        SUM(co2_kg)                            AS co2_kg
21    FROM {{core.esmiley.food_waste_daily}}
22    GROUP BY 1, 2, 4, 5
23)
24
25SELECT
26    m.month,
27    m.account_id,
28    m.account_name,
29    m.area,
30    m.currency,
31    m.registrations,
32    ROUND(m.waste_kg, 3)                                                   AS waste_kg,
33    ROUND(m.cost, 2)                                                       AS cost,
34    ROUND(m.co2_kg, 3)                                                     AS co2_kg,
35    SAFE_DIVIDE(m.waste_kg, SUM(m.waste_kg) OVER (PARTITION BY m.month, m.account_id)) AS share_of_account_kg,
36    ROUND(m.waste_kg - p.waste_kg, 3)                                      AS kg_change_vs_previous_month
37FROM monthly m
38LEFT JOIN monthly p
39  ON p.account_id = m.account_id
40 AND p.area = m.area