Fulfillment by day

1-- core.shiphero.fulfillment_by_day
2-- Fulfilment throughput, shipping cost and on-time rate per warehouse per day.
3-- Grain: day x shiphero_account x warehouse.
4-- Depends on: staging.shiphero.shipment, .shipment_line_item,
5--             .shipment_shipping_label, core.shiphero.order_fulfillment
6--
7-- Two kinds of measure, each on its own date:
8--   Throughput and cost (shipments, packages, units, labels, label_cost) land on
9--   the day each SHIPMENT was created, so an order shipped in two goes counts on
10--   both days.
11--   SLA (orders_first_shipped, orders_shipped_on_time) lands on the day of each
12--   order's FIRST shipment and counts each order once, at the warehouse that sent
13--   that first shipment.
14--
15-- Label cost is attached to its shipment and excludes refunded (voided) labels.
16-- Average cost per shipment and on-time rate are ratios: compute them in BI as
17-- SUM(label_cost) / SUM(shipments) and SUM(orders_shipped_on_time) /
18-- SUM(orders_with_required_date) over the window, never as an average of daily
19-- ratios.
20--
21-- Built on a day spine per warehouse so a day without shipments returns zeros.
22-- Days are UTC; see order_fulfillment for the time-zone note.
23
24WITH shipment_units AS (
25    SELECT shiphero_account, shipment_id, SUM(quantity) AS units
26    FROM {{staging.shiphero.shipment_line_item}}
27    GROUP BY 1, 2
28),
29
30shipment_labels AS (
31    SELECT
32        shiphero_account,
33        shipment_id,
34        COUNTIF(NOT is_refunded)                           AS labels,
35        COUNTIF(is_refunded)                               AS refunded_labels,
36        SUM(CASE WHEN NOT is_refunded THEN label_cost END) AS label_cost
37    FROM {{staging.shiphero.shipment_shipping_label}}
38    GROUP BY 1, 2
39),
40