Product sales over time

1-- core.adobe_commerce.product_sales_over_time
2-- Product-level sales with the same equation as sales_over_time, plus cost of
3-- goods where Magento recorded a cost. Covers what the admin's Products Ordered
4-- and Bestsellers reports show, with money attached.
5--
6-- Grain: day x store x order x item, top-level items only. A configurable product
7-- is one row (the parent, which carries the price and the chosen simple product's
8-- SKU), never two. A bundle is reported as the bundle, not its components.
9--
10-- Order-dated, like sales_over_time: refunds reduce the day the order was placed,
11-- because the item's refunded amounts are running totals and no credit memo
12-- tables are synced. Same order filter (no new or pending_payment orders), same
13-- time zone, same global-currency conversion.
14--
15-- Cancellations: Magento tracks canceled quantity per item but not canceled
16-- money. Ordered amounts are scaled by (qty_ordered - qty_canceled) / qty_ordered,
17-- which is exact for a fully canceled order (zero) and for whole-unit
18-- cancellations of an evenly priced line.
19--
20-- Bundles with dynamic pricing: Magento may carry the price on the component rows
21-- rather than the bundle row, and those rows are excluded here. If you sell such
22-- bundles, assert_adobe_commerce__product_sales_tie_to_orders will show it.
23--
24-- Depends on: staging.adobe_commerce.sales_order_item, .sales_order,
25--             .store_timezone
26
27WITH orders AS (
28    SELECT
29        o.adobe_commerce_store,
30        o.order_id,
31        o.order_number,
32        o.store_id,
33        o.state,
34        o.status,
35        o.global_currency_code,
36        o.base_to_global_rate,
37        DATE(o.created_at, tz.report_timezone) AS date
38    FROM {{staging.adobe_commerce.sales_order}} o
39    JOIN {{staging.adobe_commerce.store_timezone}} tz USING (adobe_commerce_store)
40    WHERE o.state NOT IN ('new', 'pending_payment')