Lane costs

1-- core.cargoson.lane_costs
2-- What it costs to move goods on each lane, by carrier: the table you take into
3-- a freight tender.
4-- Grain: cargoson_account x collection_country x delivery_country x carrier_name
5--        x freight_type x freight_currency. All time, with the date range shown.
6-- Depends on: core.cargoson.shipments
7--
8-- Cancelled shipments are excluded. A lane is country to country; Cargoson
9-- syncs city and postcode too (see core.cargoson.shipments) if you need to cut
10-- finer, but at postcode level most lanes have too few shipments for an average
11-- to mean anything.
12--
13-- freight_cost_per_kg is a ratio of sums over the shipments that have both a
14-- price and a metric weight. shipments_last_90_days is there so a lane that
15-- looked cheap three years ago and has not been used since does not set the
16-- benchmark.
17
18SELECT
19    cargoson_account,
20    collection_country,
21    delivery_country,
22    lane,
23    COALESCE(carrier_name, '(unknown carrier)') AS carrier_name,
24    freight_type,
25    freight_currency,
26    MIN(shipment_date)                                  AS first_shipment_date,
27    MAX(shipment_date)                                  AS last_shipment_date,
28    COUNT(*)                                            AS shipments,
29    COUNTIF(shipment_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)) AS shipments_last_90_days,
30    SUM(freight_cost)                                   AS freight_cost,
31    SUM(weight_kg)                                      AS weight_kg,
32    SAFE_DIVIDE(SUM(freight_cost), COUNTIF(freight_cost IS NOT NULL)) AS avg_freight_cost_per_shipment,
33    SAFE_DIVIDE(
34        SUM(IF(freight_cost IS NOT NULL AND weight_kg > 0, freight_cost, NULL)),
35        SUM(IF(freight_cost IS NOT NULL AND weight_kg > 0, weight_kg, NULL))
36    )                                                   AS freight_cost_per_kg,
37    AVG(transit_days)                                   AS avg_transit_days,
38    SAFE_DIVIDE(COUNTIF(is_on_time), COUNTIF(is_on_time IS NOT NULL)) AS on_time_rate
39FROM {{core.cargoson.shipments}}
40WHERE NOT is_cancelled