Sales over time

1-- core.tiktok_shop.sales_over_time
2-- Sales over time for a TikTok shop: the sales equation per day, with shipping
3-- and tax, built from orders and returns.
4--
5-- Grain: day x account x shop x currency x row type (SALE, CANCELLATION,
6-- RETURN). Keeping row type in the grain lets the sale side and the reversal side
7-- be checked independently; sum over it in BI. Dates are the shop's local day
8-- (see staging.tiktok_shop.shop), and every event lands on the day it happened:
9-- sales on payment, cancellations on cancellation, refunds on completion.
10--
11-- Product columns use exactly the same rules as
12-- core.tiktok_shop.product_sales_over_time, and the two reconcile on net_sales
13-- and units (dbt/tests/assert_tiktok_shop__product_reconciles_to_sales.sql). Keep the two in
14-- step when changing either.
15--
16--   net_sales   = gross_sales + seller_discounts + platform_discounts
17--               + cancellations + returns
18--   total_sales = net_sales + shipping + taxes
19--
20-- Shipping and tax are order-level. They are reversed when a whole order is
21-- cancelled, and refunded shipping and tax are taken from the return lines. A
22-- single cancelled line inside a shipped order does not reverse any shipping.
23--
24-- This is the customer-facing view of sales. For what TikTok actually paid out
25-- after commission, fees and shipping, use core.tiktok_shop.settlement_ledger.
26-- For TikTok's own GMV figure, use core.tiktok_shop.shop_performance.
27--
28-- Depends on: staging.tiktok_shop.order, .order_line_items, .return_order,
29--             .return_order_line_items, .shop
30
31WITH shop AS (
32    SELECT tiktok_account, shop_id, ANY_VALUE(report_timezone) AS report_timezone
33    FROM {{staging.tiktok_shop.shop}}
34    GROUP BY tiktok_account, shop_id
35),
36
37-- Same filter as product_sales_over_time: no unpaid, sample or replacement orders.
38orders AS (
39    SELECT o.*, s.report_timezone
40    FROM {{staging.tiktok_shop.order}} o