HAVING clause to filter groups by an aggregate condition
Detention charges are killing our budget. I need lanes where the average `dwell_hours_at_origin` exceeds 2.0 hours. Query `fact_shipments`, group by `lane_id`, compute avg dwell as `avg_dwell_hrs` (round to 2 decimals), count shipments as `shipment_count`. Use HAVING to filter. Sort by avg_dwell_hrs 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 |
Each hint you reveal reduces the XP you can earn. Try the query first.
HAVING filters on aggregate results — unlike WHERE which filters individual rows, HAVING filters groups after GROUP BY.
Filter the grouped result, not the rows — there's a clause that runs after GROUP BY and accepts aggregate expressions.
GROUP BY lane_id and sort by avg_dwell_hrs DESC.