Sales over time

1-- core.bigcommerce.sales_over_time
2-- The sales equation per order per day, with refunds
3-- on the day they were made.
4--
5-- Grain: day x store x order x row type. Row type is SALE (the order, on the day it
6-- was placed) or RETURN (refunds, on the day they were issued). Keeping it in the
7-- grain means an order that sells and refunds produces separate rows, so both sides
8-- stay auditable. Aggregate to day in BI.
9--
10-- Currency: the order's own (currency_code), which is in the grain via the order.
11-- Do not sum across currencies in a multi-currency store without converting first.
12-- Timezone: staging.bigcommerce.store.report_timezone.
13--
14-- Depends on: staging.bigcommerce.order, .refund, .refund_line_item,
15--             .refund_payment, .channel, .store
16--
17-- WHICH ORDERS COUNT. Dropped: Incomplete (0, an abandoned checkout that never
18-- became an order), Cancelled (5), Declined (6, payment failed) and archived
19-- orders. Everything else stays, including Pending and Awaiting Payment, because
20-- BigCommerce has created the order and the merchant usually expects to be paid;
21-- status is passed through so BI can narrow it. Refunds are kept only for orders
22-- that are kept, so a refund can never appear without its sale.
23--
24-- THE SALES EQUATION. Discounts are not taken from discount_amount or
25-- coupon_discount: the API documents neither precisely enough to know whether one
26-- contains the other. They are derived from BigCommerce's own totals instead,
27--     discounts = total_ex_tax - subtotal_ex_tax - shipping - handling - wrapping
28-- which makes total_sales on a SALE row equal BigCommerce's total_inc_tax by
29-- construction. Both reported discount columns are passed through for comparison.
30
31WITH store AS (
32    SELECT bigcommerce_store, report_timezone
33    FROM {{staging.bigcommerce.store}}
34),
35
36orders AS (
37    SELECT
38        o.*,
39        c.channel_name,
40        c.channel_type,