Drill 5 of 12 in Logistics questions
0

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…