Food waste daily

1-- core.esmiley.food_waste_daily
2-- Food waste per day, account and registration point: weight, value and CO2.
3-- The figures behind eSmiley's dashboard, as a table.
4-- Grain: registration date x account x registration point.
5--
6-- Dates are the UTC date of the registration time. Cost is in the currency on the
7-- registration; group by currency before summing across accounts. See
8-- staging.esmiley.registration for how weight, cost and CO2 are converted.
9
10SELECT
11    r.registration_date,
12    r.account_id,
13    a.account_name,
14    r.registration_point_id,
15    COALESCE(t.registration_point_name, ANY_VALUE(r.registration_point_name)) AS registration_point_name,
16    t.area,
17    t.path,
18    r.currency,
19    COUNT(*)                         AS registrations,
20    ROUND(SUM(r.waste_kg), 3)        AS waste_kg,
21    ROUND(SUM(r.cost), 2)            AS cost,
22    ROUND(SUM(r.co2_kg), 3)          AS co2_kg
23FROM {{staging.esmiley.registration}} r
24LEFT JOIN {{core.esmiley.registration_point_tree}} t USING (registration_point_id)
25LEFT JOIN {{staging.esmiley.account}} a ON a.account_id = r.account_id
26GROUP BY r.registration_date, r.account_id, a.account_name, r.registration_point_id,
27         t.registration_point_name, t.area, t.path, r.currency