Product sales over time

1-- core.bigcommerce.product_sales_over_time
2-- The sales equation per order line, with
3-- product refunds on the day they were made.
4--
5-- Grain: day x store x order x order line x row type (SALE or RETURN).
6-- Currency: the order's own. Timezone: staging.bigcommerce.store.report_timezone.
7--
8-- Depends on: staging.bigcommerce.order, .order_line_item,
9--             .order_line_item_discount, .refund, .refund_line_item,
10--             .refund_payment, .store
11--
12-- RECONCILES TO sales_over_time ON SALE ROWS. Line discounts come from
13-- order_line_item_discount. Whatever part of the order's discount is not attached
14-- to a line (a manual order discount, for instance) is spread across the order's
15-- lines in proportion to their gross value, so net_sales here sums to net_sales in
16-- sales_over_time for every order. tests/assert_bigcommerce__product_reconciles_to_sales.sql
17-- checks it.
18--
19-- RETURN ROWS ARE AN ESTIMATE, AND DO NOT RECONCILE. BigCommerce refunds products
20-- by quantity and does not store the money per line, so a returned unit is valued
21-- at the line's net unit price, tax excluded (is_return_value_estimated = TRUE).
22-- Where a PRODUCT refund line carries an explicit requested_amount and no
23-- quantity, that amount is used as given. Refunds of shipping, handling or a
24-- lump sum against the whole order have no product and are only in
25-- sales_over_time.
26--
27-- Same order filter as sales_over_time: Incomplete, Cancelled, Declined and
28-- archived orders are dropped.
29
30WITH store AS (
31    SELECT bigcommerce_store, report_timezone
32    FROM {{staging.bigcommerce.store}}
33),
34
35orders AS (
36    SELECT
37        o.bigcommerce_store,
38        o.order_id,
39        o.created_at,
40        o.currency_code,