JOIN fact to dim and GROUP BY a dim attribute to compute an average metric
I need average total cost per shipment broken out by freight mode. Join `fact_shipments` to `dim_carriers`, compute avg of (freight_cost + fuel_surcharge + accessorial_cost) as `avg_total_cost`, group by `mode`, round to 2 decimals. Sort by avg_total_cost descending.
| 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.
Join fact_shipments to dim_carriers on carrier_id to get the mode column.
Total cost per shipment = freight_cost + fuel_surcharge + accessorial_cost. Wrap in AVG() and ROUND().
GROUP BY c.mode to get one row per freight mode (TL, LTL, PARCEL).