Sales over time

1-- core.walmart.sales_over_time
2-- Sales over time for a Walmart Marketplace seller, from the Orders API: the
3-- sales equation per order day, with shipping and tax.
4--
5-- Grain: order day x seller x currency x ship node type. ship_node_type splits
6-- seller-fulfilled from Walmart Fulfillment Services (WFS) and 3PL orders. The
7-- day is the order date in the seller's timezone (staging.walmart.seller).
8--
9-- ORDER-DATE VIEW. The Orders API records what was cancelled and refunded on a
10-- line but not when, so both are attributed back to the order day. Recent days
11-- keep moving as refunds arrive; history from months ago is stable. For refunds
12-- dated when the money moved, and for what Walmart actually paid after
13-- commission, use core.walmart.settlement_ledger.
14--
15--   net_sales   = gross_sales + cancellations + refunds
16--   total_sales = net_sales + shipping + other_charges + taxes
17--
18-- Product columns follow exactly the same rules as
19-- core.walmart.product_sales_over_time and reconcile with it
20-- (dbt/tests/assert_walmart__product_reconciles_to_sales.sql). Keep the two in step.
21-- Shipping, other charges and tax are reduced by the cancelled share of their
22-- line and by their own refunded amounts.
23--
24-- Depends on: staging.walmart.order, .order_line, .order_line_charge,
25--             .order_line_status, .order_line_refund_charge, .seller
26
27WITH seller AS (
28    SELECT walmart_seller, ANY_VALUE(report_timezone) AS report_timezone
29    FROM {{staging.walmart.seller}}
30    GROUP BY walmart_seller
31),
32
33charges AS (
34    SELECT
35        walmart_seller, purchase_order_id, line_number,
36        SUM(CASE WHEN charge_type = 'PRODUCT'  THEN charge_amount ELSE 0 END)          AS product_amount,
37        SUM(CASE WHEN charge_type = 'SHIPPING' THEN charge_amount ELSE 0 END)          AS shipping_amount,
38        SUM(CASE WHEN charge_type NOT IN ('PRODUCT', 'SHIPPING') OR charge_type IS NULL
39                 THEN charge_amount ELSE 0 END)                                         AS other_amount,
40        SUM(tax_amount)                                                                 AS tax_amount