JOIN a fact table to a dimension table and GROUP BY a dim column
I need total shipment volume by carrier. Join `fact_shipments` to `dim_carriers`, group by `carrier_name`, count shipments as `total_shipments`, sort descending. This tells us who's carrying the most freight.
| 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.
Carrier name lives on the dim, shipment volume on the fact — bridge them.
There's a clause that collapses rows so you get one per carrier.
Then rank the result by volume, biggest first.