Product sales over time

1-- core.woocommerce.product_sales_over_time
2-- Recreates WooCommerce Analytics' Products and Variations reports (items sold,
3-- net sales, orders) at line grain, with the same sales equation as
4-- sales_over_time.
5--
6-- Grain: day x store x order x line. A line is one row of wc_order_product_lookup:
7-- a product line on an order (row_type SALE) or on a refund (row_type RETURN).
8-- order_id is always the ORIGINAL order, so a refunded line and the line it
9-- reverses share an order_id but not a line id.
10--
11-- Same report date and status rules as sales_over_time (read from the store's
12-- analytics settings, applied through wc_order_stats), so the two agree on which
13-- orders count and on which day.
14--
15-- Product lines only. Shipping lines, fees and order-level refund amounts that are
16-- not tied to a product are not in the lookup table, so net_sales here sums to
17-- less than sales_over_time.net_sales whenever a store charges fees or refunds a
18-- lump sum. Discounts do tie exactly: sales_over_time takes its coupons from these
19-- same lines.
20--
21-- Orders per product: COUNT(DISTINCT order_id) over SALE rows, which is the
22-- Products report's "Orders" column. Not a column here, because a distinct count
23-- does not survive being summed across days.
24--
25-- Names: product_name is the current catalog name; line_item_name is the name as
26-- it read on the order and survives the product being renamed or deleted.
27--
28-- Depends on: staging.woocommerce.order_product_lookup, .order_stats,
29--             .order_items, .product, .product_sku, .store_settings
30
31WITH settings AS (
32    SELECT
33        woocommerce_store,
34        currency,
35        COALESCE(analytics_date_type, 'date_paid') AS date_type,
36        ARRAY_CONCAT(
37            ['wc-auto-draft', 'wc-trash'],
38            COALESCE(excluded_statuses, ['wc-pending', 'wc-failed', 'wc-cancelled'])
39        ) AS excluded_statuses
40    FROM {{staging.woocommerce.store_settings}}