Order fulfillment

1-- core.shiphero.order_fulfillment
2-- Every order with when it shipped, whether it met its required ship date, what
3-- the labels cost and what the customer was charged for shipping.
4-- Grain: one row per shiphero_account x order.
5-- Depends on: staging.shiphero.order, .order_line_item, .order_shipping_line,
6--             .shipment, .shipment_shipping_label
7--
8-- This is the order-level SLA view: ShipHero's "orders shipped late" and
9-- "time to ship" questions, plus shipping cost per order. fulfillment_by_day reads
10-- it for the daily on-time rate.
11--
12-- Every child table is aggregated to the order BEFORE it is joined. An order with
13-- three shipments and five line items would otherwise produce fifteen rows and
14-- triple the units and quintuple the label cost.
15--
16-- SHIPPED means a shipment exists. first_shipped_at is the earliest shipment's
17-- creation time, which is when the first package was labelled.
18--
19-- ON TIME compares the date of the first shipment with ShipHero's
20-- required_ship_date. It is NULL, deliberately, for orders without a required ship
21-- date or not shipped yet: there is nothing to be on time against. Use
22-- is_late_unshipped for open orders already past their date; orders marked
23-- fulfilled without a synced shipment (shipped outside ShipHero) are not counted
24-- as late.
25--
26-- TIME ZONE: timestamps are UTC as delivered. A warehouse in a US time zone
27-- will see late-evening shipments on the next UTC day; convert with
28-- DATETIME(ts, 'America/New_York') if same-day SLAs are measured locally.
29
30WITH lines AS (
31    SELECT
32        shiphero_account,
33        order_id,
34        COUNT(*)              AS line_items,
35        SUM(quantity)         AS units_ordered,
36        SUM(quantity_shipped) AS units_shipped,
37        SUM(backorder_quantity) AS units_backordered
38    FROM {{staging.shiphero.order_line_item}}
39    GROUP BY 1, 2
40),