Sales over time

1-- core.dandomain_webshop.sales_over_time
2-- The sales equation per order per day, excluding VAT, with credit notes as
3-- returns on the day they were issued.
4--
5-- Grain: day x shop x order. A credit note is its own order in DanDomain, so it
6-- has its own row, typed RETURN; ordinary orders are typed SALE.
7-- Currency: the order's own (currency_iso). Do not sum across currencies.
8-- Dates: shop-local, as DanDomain returns them; see staging.order.
9--
10-- Depends on: staging.dandomain_webshop.order, .order_line, .order_discount_code,
11--             .order_transaction, .order_status_code
12--
13-- WHICH ORDERS COUNT. Dropped: 0 Not received, 5 Cancelled and 99 Draft. Status
14-- 100 is a credit note and becomes a RETURN row. Everything else is a SALE,
15-- including Order received and Processing.
16--
17-- RETURNS ARE CREDIT NOTES, AND ONLY CREDIT NOTES. A credit note is valued from its
18-- own lines and forced negative, whichever sign the shop stored it with, so it
19-- always reduces sales. The connector syncs no link from a credit note to the
20-- order it credits, so a return cannot be traced to its original sale here.
21-- Refunds made only at the payment gateway, with no credit note, are not in the
22-- sales equation; refunded_payments shows them on the original order's row, dated
23-- on the order, because DanDomain records no refund date.
24--
25-- WHY THE EQUATION IS VAT-EXCLUSIVE. DanDomain documents line prices and discounts
26-- without VAT, and shipping and payment fees with only a has-VAT flag. A
27-- VAT-inclusive total would rest on assumptions about how VAT is applied to
28-- discount codes, which the API does not document. line_vat is the VAT recorded on
29-- the lines, passed through as stored.
30
31WITH orders AS (
32    SELECT
33        o.*,
34        IF(o.status_id = 100, 'RETURN', 'SALE')            AS row_type,
35        IF(o.status_id = 100, -1, 1)                       AS sign_factor,
36        sc.status_title
37    FROM {{staging.dandomain_webshop.order}} o
38    LEFT JOIN {{staging.dandomain_webshop.order_status_code}} sc USING (dandomain_shop, status_id)
39    WHERE COALESCE(o.status_id, -1) NOT IN (0, 5, 99)
40),