Invoice costs monthly

1-- core.daisycon.invoice_costs_monthly
2-- Invoiced affiliate costs per month and invoice line type: what you actually pay
3-- Daisycon, split into commission, fees and whatever else the lines say.
4--
5-- Grain: invoice_month x advertiser_id x currency x line_code. Currency: the invoice's.
6-- Depends on: staging.daisycon.financial_invoice_line, .financial_invoice, .advertiser
7--
8-- Months are the invoice date, not the period the commission was earned in; use
9-- period_start_date on core.daisycon.invoices if you need the latter. Line codes are
10-- Daisycon's own; the description is the most recent one seen for the code.
11
12SELECT
13    DATE_TRUNC(i.invoice_date, MONTH)  AS invoice_month,
14    l.advertiser_id,
15    ANY_VALUE(a.advertiser_name)       AS advertiser_name,
16    i.currency,
17    l.line_code,
18    ARRAY_AGG(l.line_description IGNORE NULLS ORDER BY i.invoice_date DESC LIMIT 1)[SAFE_OFFSET(0)] AS line_description,
19    COUNT(DISTINCT l.invoice_number)   AS invoices,
20    SUM(l.quantity)                    AS quantity,
21    SUM(l.amount_excl_vat)             AS amount_excl_vat,
22    SUM(l.vat)                         AS vat,
23    SUM(l.amount_incl_vat)             AS amount_incl_vat
24FROM {{staging.daisycon.financial_invoice_line}} l
25-- Both lookups are one row per key after staging, so these joins cannot fan out.
26JOIN {{staging.daisycon.financial_invoice}} i
27    ON i.advertiser_id = l.advertiser_id AND i.invoice_number = l.invoice_number
28LEFT JOIN {{staging.daisycon.advertiser}} a
29    ON a.advertiser_id = l.advertiser_id
30GROUP BY invoice_month, l.advertiser_id, i.currency, l.line_code