Calculate OTD rate using CASE WHEN inside AVG for a conditional percentage
Now for the real scorecard. Join `fact_shipments` to `dim_carriers`. For delivered shipments only, compute OTD rate per carrier: a shipment is on-time if `actual_delivery_date <= planned_delivery_date`. Label the rate `otd_rate`, round to 4 decimal places. Include `carrier_name` and `total_delivered`. Sort by otd_rate ascending so worst performers are first.
| Column | Type | Key |
|---|---|---|
| shipment_id | INT | PK |
| carrier_id | INT | FK → dim_carriers |
| lane_id | INT | FK → dim_lanes |
| planned_pickup_date | TEXT | |
| actual_pickup_date | TEXT | |
| planned_delivery_date | TEXT | |
| actual_delivery_date | TEXT | |
| qty_ordered | INT | |
| qty_shipped | INT | |
| weight_lbs | REAL | |
| freight_cost | REAL | |
| fuel_surcharge | REAL | |
| accessorial_cost | REAL | |
| dwell_hours_at_origin | REAL | |
| status | TEXT |
| Column | Type | Key |
|---|---|---|
| carrier_id | INT | PK |
| carrier_name | TEXT | |
| scac | TEXT | |
| mode | TEXT | |
| on_time_target_pct | REAL | |
| hq_state | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Filter to status = 'delivered' before computing OTD — in_transit and failed shipments have no valid final delivery date.
Conditional aggregation: turn a boolean (was-on-time) into 1.0/0.0 inside an aggregate to compute a rate.
GROUP BY carrier_name, sort by otd_rate ASC so worst performers float to the top.