Product sales over time

1-- core.salesforce_commerce_cloud.product_sales_over_time
2-- The sales equation per product line, on the day the order was placed.
3--
4-- Grain: day x instance x site x order x product line. Currency: the order's own.
5-- Timezone: staging.salesforce_commerce_cloud.settings.report_timezone.
6--
7-- Depends on: staging.salesforce_commerce_cloud.order, .order_product_item, .settings
8--
9-- Same order filter as sales_over_time (new, open, completed), and the same
10-- limitation: no returns are synced, so there are no RETURN rows.
11--
12-- Order-level discounts are already allocated to lines by Commerce Cloud itself
13-- (price_after_order_discount), so net_sales here sums to net_sales in
14-- sales_over_time without any allocation of our own.
15-- tests/assert_salesforce_commerce_cloud__product_reconciles_to_sales.sql checks it.
16
17WITH settings AS (
18    SELECT sfcc_instance, report_timezone
19    FROM {{staging.salesforce_commerce_cloud.settings}}
20)
21
22SELECT
23    DATE(COALESCE(o.placed_at, o.created_at), s.report_timezone)   AS date,
24    o.sfcc_instance,
25    o.site_id,
26    o.order_no,
27    i.item_position,
28    i.item_id,
29    i.product_id,
30    i.product_name,
31    i.is_bonus_item,
32    o.currency,
33    o.taxation = 'gross'                                            AS prices_include_tax,
34    o.status,
35
36    i.quantity,
37    i.base_price                                                    AS unit_price,
38    i.price                                                         AS gross_sales,
39    i.price_after_item_discount - i.price                           AS item_discounts,
40    i.price_after_order_discount - i.price_after_item_discount      AS order_discounts,