Sales over time

1-- core.salesforce_commerce_cloud.sales_over_time
2-- The sales equation per order, on the day the order was placed.
3--
4-- Grain: day x instance x site x order. Currency: the order's own (`currency`).
5-- Timezone: staging.salesforce_commerce_cloud.settings.report_timezone.
6--
7-- Depends on: staging.salesforce_commerce_cloud.order, .order_product_item, .settings
8--
9-- THERE ARE NO RETURN ROWS, because the connector syncs no returns, refunds or
10-- payment transactions: the order record is all there is. Cancelling an order in
11-- Commerce Cloud changes its status, and this model then drops it, so a cancelled
12-- order disappears from the day it was placed rather than appearing as a reversal
13-- on the day it was cancelled. History restates. If you need returns, they live in
14-- your order management system, not in this connector.
15--
16-- WHICH ORDERS COUNT. new, open and completed. Dropped: created (not yet placed,
17-- typically payment still in progress), failed, cancelled and replaced (an order
18-- edited by replacement; its successor is a new order, so keeping both would count
19-- the sale twice).
20--
21-- TAX BASIS. Gross-taxation sites price tax-inclusive, net-taxation sites do not.
22-- The equation below is in the site's own basis, as Business Manager shows it, and
23-- the *_ex_tax columns are comparable across both.
24
25WITH settings AS (
26    SELECT sfcc_instance, report_timezone
27    FROM {{staging.salesforce_commerce_cloud.settings}}
28),
29
30-- Pre-aggregated to one row per order, so joining lines cannot fan out the header
31-- money.
32items AS (
33    SELECT
34        sfcc_instance,
35        order_no,
36        SUM(price)                                          AS gross_sales,
37        SUM(price_after_item_discount - price)              AS item_discounts,
38        SUM(quantity)                                       AS quantity,
39        COUNT(*)                                            AS product_lines
40    FROM {{staging.salesforce_commerce_cloud.order_product_item}}