Invoiced sales over time

1-- core.traede.invoiced_sales_over_time
2-- Invoiced wholesale revenue per day, from booked Traede invoices.
3-- Grain: day x traede_account x currency. Amounts ex VAT unless named otherwise.
4-- Depends on: staging.traede.invoice
5--
6-- WHY INVOICES, NOT ORDERS: in wholesale an order is booked months before it ships
7-- and is often shipped in drops, part-cancelled or re-sized on the way. Revenue is
8-- what was invoiced. Order intake and the open order book are in core.traede.order_book.
9--
10-- Only BOOKED invoices count. A draft has no invoice number, can still change and is
11-- not revenue. Draft totals are returned separately so a backlog of unbooked drafts
12-- is visible rather than silently missing.
13--
14-- CURRENCY is part of the grain because Traede invoices each customer in its own
15-- currency and nothing in the synced data converts between them. Summing across
16-- currencies gives a meaningless number; convert in BI or join your own FX table.
17--
18-- CREDIT NOTES ARE NOT DEDUCTED. They are a separate Traede resource the connector
19-- does not sync, so this is gross invoiced revenue before credits.
20--
21-- Built on a day spine per account and currency so a day with no invoices returns
22-- zeros instead of a gap a line chart would interpolate across.
23
24WITH booked AS (
25    SELECT *
26    FROM {{staging.traede.invoice}}
27    WHERE is_booked
28),
29
30series AS (
31    -- One series per account and currency, from its first booked invoice to today,
32    -- or to the last invoice date if an invoice is dated ahead.
33    SELECT traede_account, currency, MIN(invoice_date) AS first_date, MAX(invoice_date) AS last_date
34    FROM booked
35    GROUP BY 1, 2
36),
37
38spine AS (
39    SELECT s.traede_account, s.currency, d AS date
40    FROM series s