Compute a conditional rate using CASE WHEN on two columns compared
In-full rate next — that's the share of shipments where `qty_shipped = qty_ordered`. Compute it per carrier across ALL shipments (not just delivered), label it `infull_rate`, round to 4 decimal places. Include total_shipments. Sort by infull_rate ascending.
| 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.
In-full = qty_shipped equals qty_ordered. Use CASE WHEN qty_shipped = qty_ordered THEN 1.0 ELSE 0.0 END inside AVG.
No status filter this time — compute across all shipments in the table.
GROUP BY carrier_name, sort by infull_rate ASC.