Carrier costs by month

1-- core.cargoson.carrier_costs_by_month
2-- Shipments, freight spend, weight moved and delivery performance per carrier
3-- and service, per month.
4-- Grain: month x cargoson_account x carrier_name x service_name x freight_currency.
5-- Currency: freight_currency is in the grain, so a carrier billed in two
6-- currencies returns two rows. Never sum freight_cost across currencies.
7-- Depends on: core.cargoson.shipments
8--
9-- A shipment lands in the month of shipment_date: collection, falling back to
10-- the booking date. Cancelled shipments are counted in cancelled_shipments and
11-- excluded from everything else.
12--
13-- RATES ARE RATIOS OF SUMS. freight_cost_per_kg is total cost over total kg of
14-- the shipments that have BOTH a price and a metric weight, so one 2 kg parcel
15-- does not count as much as a 20 tonne truck. on_time_rate divides by the
16-- shipments where on-time could be judged, not by every shipment.
17--
18-- Built on a month spine per carrier and service, from the first month the
19-- combination appears to the current month (or its latest planned month), so a carrier with a quiet month
20-- returns zeros instead of vanishing from a trend line.
21
22WITH shipments AS (
23    SELECT
24        *,
25        DATE_TRUNC(shipment_date, MONTH) AS month,
26        COALESCE(carrier_name, '(unknown carrier)') AS carrier_label,
27        COALESCE(service_name, '(unknown service)') AS service_label
28    FROM {{core.cargoson.shipments}}
29    WHERE shipment_date IS NOT NULL
30),
31
32series AS (
33    SELECT
34        cargoson_account,
35        carrier_label,
36        service_label,
37        freight_currency,
38        MIN(month) AS first_month,
39        MAX(month) AS last_month
40    FROM shipments