Product sales over time

1-- core.tiktok_shop.product_sales_over_time
2-- Sales by product and SKU over time, event-based, and reconciling to
3-- core.tiktok_shop.sales_over_time on net_sales and units.
4--
5-- Grain: day x account x shop x currency x product x SKU x row type, where row
6-- type is SALE, CANCELLATION or RETURN. Each event lands on the day it happened
7-- in the shop's local time (see staging.tiktok_shop.shop):
8--   SALE          the order's paid time (created time for cash on delivery)
9--   CANCELLATION  the order's cancel time, for every line TikTok marks CANCELLED
10--   RETURN        the day a return or refund request completed
11-- So a June order refunded in July reduces July, and history does not restate.
12--
13-- ONE LINE ITEM IS ONE UNIT. TikTok has no quantity column: units are counted.
14--
15-- The sales equation, all reversals stored negative so it is plain addition:
16--   net_sales = gross_sales + seller_discounts + platform_discounts
17--             + cancellations + returns
18-- net_sales is what customers paid for products. platform_discounts are funded
19-- by TikTok and paid back to you in settlement, so seller revenue before fees is
20-- net_sales - platform_discounts; core.tiktok_shop.settlement_ledger has the
21-- payout view.
22--
23-- Left out on purpose: unpaid orders (no money yet), free sample orders sent to
24-- creators and free replacement orders (they add units at zero price).
25--
26-- Depends on: staging.tiktok_shop.order, .order_line_items, .return_order,
27--             .return_order_line_items, .shop
28
29WITH shop AS (
30    SELECT tiktok_account, shop_id, ANY_VALUE(report_timezone) AS report_timezone
31    FROM {{staging.tiktok_shop.shop}}
32    GROUP BY tiktok_account, shop_id
33),
34
35orders AS (
36    SELECT o.*, s.report_timezone
37    FROM {{staging.tiktok_shop.order}} o
38    JOIN shop s USING (tiktok_account, shop_id)
39    WHERE o.order_status != 'UNPAID'
40      AND (o.paid_at IS NOT NULL OR o.is_cod)