Interview Question Bank · CASE bands · ratio of sums · sort key for labels
5. Cost per kg by carrier and weight band
MediumLogisticsQuestion #161
Concept: CASE bands · ratio of sums · sort key for labelsshow
Bucket a number with CASE WHEN x < a THEN ... WHEN x < b THEN ... ELSE ... END; checking the upper bounds in order makes each boundary value land in exactly one band. A rate like cost per kg is total cost divided by total kg, not the average of each row's rate, which would give a 1 kg parcel the same say as a 40 kg pallet. Text labels sort alphabetically, so keep a numeric band key for ORDER BY.
Example (different data):
SELECT CASE WHEN km < 10 THEN 1 WHEN km < 50 THEN 2 ELSE 3 END AS band, ROUND(SUM(fare) / SUM(km), 2) AS fare_per_km FROM taxi_trips GROUP BY 1 ORDER BY 1;
Your task
Larkspur Freight's procurement team is comparing carrier rates and suspects some carriers are cheap only for heavy freight. Weight bands: under 5 kg is '0-5 kg', 5 kg up to (not including) 20 kg is '5-20 kg', and 20 kg or more is '20+ kg'.
Across all shipments, for each carrier and weight band that has shipments, return carrier (name), weight_band, shipments, total_kg (sum of weight_kg) and cost_per_kg (total cost / total kg, rounded to 2 decimals). Sort by carrier, then weight band from lightest to heaviest.
Starting Postgres in your browser…
Checking your access…