Product sales over time

1-- core.dandomain_webshop.product_sales_over_time
2-- Sales per order line, excluding VAT, with credit-note lines as returns.
3--
4-- Grain: day x shop x order x order line. row_type is SALE, or RETURN for the
5-- lines of a credit note. Currency: the order's own. Dates: shop-local.
6--
7-- Depends on: staging.dandomain_webshop.order, .order_line, .order_discount_code
8--
9-- Same order filter as sales_over_time. Discount codes apply to the whole order,
10-- so they are spread across its lines in proportion to each line's value after
11-- line discounts; with that, net_sales here sums to net_sales in sales_over_time.
12-- tests/assert_dandomain_webshop__product_reconciles_to_sales.sql checks it. Shipping and payment
13-- fees have no product and stay in sales_over_time only.
14
15WITH orders AS (
16    SELECT
17        dandomain_shop,
18        order_id,
19        order_created_at,
20        status_id,
21        currency_iso,
22        IF(status_id = 100, 'RETURN', 'SALE')              AS row_type,
23        IF(status_id = 100, -1, 1)                         AS sign_factor
24    FROM {{staging.dandomain_webshop.order}}
25    WHERE COALESCE(status_id, -1) NOT IN (0, 5, 99)
26),
27
28codes AS (
29    SELECT dandomain_shop, order_id, SUM(ABS(discount_amount)) AS discount_codes
30    FROM {{staging.dandomain_webshop.order_discount_code}}
31    GROUP BY dandomain_shop, order_id
32),
33
34lines AS (
35    SELECT
36        l.*,
37        ABS(l.unit_price * l.quantity)                     AS line_gross,
38        ABS(COALESCE(l.unit_discount, 0) * l.quantity)     AS line_discount,
39        o.order_created_at,
40        o.status_id,