Product sales over time

1-- core.walmart.product_sales_over_time
2-- Sales by SKU per order day, from the Orders API, reconciling to
3-- core.walmart.sales_over_time on net sales and units.
4--
5-- Grain: order day x seller x currency x ship node type x SKU. The day is the
6-- order date in the seller's timezone (staging.walmart.seller).
7--
8-- ORDER-DATE VIEW, NOT AN EVENT LOG. Walmart's Orders API says what was
9-- cancelled and refunded on each line but not when, so cancellations and refunds
10-- are attributed back to the day the order was placed. A May order refunded in
11-- July lowers May, and recent days keep moving as refunds arrive. For refunds on
12-- the day the money moved, use core.walmart.settlement_ledger.
13--
14-- The sales equation, reversals negative so it is plain addition:
15--   net_sales = gross_sales + cancellations + refunds
16-- gross_sales is the PRODUCT charge on every line. A cancelled share of a line
17-- reverses the same share of its charge. Refunds are the refunded PRODUCT
18-- charges; a line cancelled in full has its refunds ignored so it is not reversed
19-- twice.
20--
21-- Depends on: staging.walmart.order, .order_line, .order_line_charge,
22--             .order_line_status, .order_line_refund_charge, .seller
23
24WITH seller AS (
25    SELECT walmart_seller, ANY_VALUE(report_timezone) AS report_timezone
26    FROM {{staging.walmart.seller}}
27    GROUP BY walmart_seller
28),
29
30charges AS (
31    SELECT
32        walmart_seller, purchase_order_id, line_number,
33        SUM(CASE WHEN charge_type = 'PRODUCT' THEN charge_amount ELSE 0 END) AS product_amount
34    FROM {{staging.walmart.order_line_charge}}
35    GROUP BY 1, 2, 3
36),
37
38cancelled AS (
39    SELECT
40        walmart_seller, purchase_order_id, line_number,