Billing by month

1-- core.chargebee.billing_by_month
2-- What was invoiced, credited, paid and written off, by month. The cash-side
3-- companion to the MRR models, and the closest match to Chargebee's invoice and
4-- credit note exports totalled by month.
5--
6-- Grain: month x currency. Currency: the document currency, major units.
7-- Invoices are dated on the invoice date, credit notes on the credit note date.
8-- Voided invoices and voided credit notes are excluded.
9-- Depends on: staging.chargebee.invoice, invoice_line_item, credit_note
10--
11-- THE EQUATION, all ex tax:
12--   gross_invoiced - invoice_discounts = invoiced_sub_total
13--   invoiced_sub_total - credit_notes_sub_total = net_billings
14-- `invoice_total` adds tax back on, so it is what customers were asked to pay.
15--
16-- Each source is aggregated to month x currency on its own before the join, so
17-- no invoice is counted once per line.
18
19WITH invoices AS (
20    SELECT
21        DATE_TRUNC(invoice_date, MONTH)                 AS month,
22        currency,
23        COUNT(*)                                        AS invoices,
24        SUM(sub_total)                                  AS invoiced_sub_total,
25        SUM(IF(is_recurring, sub_total, 0))             AS recurring_invoiced_sub_total,
26        SUM(tax)                                        AS invoice_tax,
27        SUM(total)                                      AS invoice_total,
28        SUM(amount_paid)                                AS amount_paid,
29        SUM(credits_applied)                            AS credits_applied,
30        SUM(write_off_amount)                           AS written_off,
31        SUM(amount_due)                                 AS amount_due
32    FROM {{staging.chargebee.invoice}}
33    WHERE status <> 'voided'
34    GROUP BY 1, 2
35),
36
37invoice_lines AS (
38    SELECT
39        DATE_TRUNC(i.invoice_date, MONTH)               AS month,
40        i.currency,