Sales Over Time

1-- shopify_sales_over_time - recreates Shopify's "Total sales over time"
2--
3-- Grain: day x store x order x location x channel x row type. Keeping row type
4-- in the grain means a day where an order both sells and refunds produces two
5-- rows, so the SALE and RETURN sides stay independently auditable.
6--
7-- Multi-store safe: order IDs are only unique WITHIN a store, so shopify_store is
8-- part of every join key and of the grain. Drop it from one join and rows fan out
9-- across storefronts silently.
10--
11-- Event-based: reads Shopify's order agreement log, so every financial change
12-- lands on the day it happened - returns on the refund date, order edits on the
13-- edit date. History does not restate itself when an old order is refunded.
14--
15-- Currency: shop money, plus the presentment amounts the customer actually paid.
16-- Timezone: read from the shop record, so nothing needs hardcoding.
17--
18-- Depends on: staging.shopify.order, .order_agreement, .order_agreement_sale,
19--             .order_line, .order_refund, .order_line_refund, .shop, .location
20
21WITH shop AS (
22    -- Per store: currency and timezone both vary between storefronts.
23    SELECT
24        shopify_store,
25        ANY_VALUE(currency)                       AS shop_currency,
26        COALESCE(ANY_VALUE(iana_timezone), 'UTC') AS report_timezone
27    FROM {{staging.shopify.shop}}
28    GROUP BY shopify_store
29),
30
31locations AS (
32    SELECT shopify_store, location_id, location_name, location_country_code
33    FROM {{staging.shopify.location}}
34),
35
36orders AS (
37    SELECT
38        o.shopify_store,
39        o.order_id,
40        o.order_name,