Drill 12 of 12 in Logistics questions
0

Interview Question Bank · Pre-aggregate before joining · LEFT JOIN · join fan-out

12. Fill rate per customer

HardLogisticsQuestion #168

Concept: Pre-aggregate before joining · LEFT JOIN · join fan-outshow

When one row (an order line) can match several rows in another table (its partial shipments), joining them repeats the first row once per match, and any SUM over its columns is inflated. Aggregate the many side to one row per key first, in a CTE, then join. Use a LEFT JOIN so lines that never shipped still count in the denominator.

Example (different data):

WITH paid AS (
  SELECT invoice_id, SUM(amount) AS paid
  FROM payments GROUP BY invoice_id
)
SELECT i.client, SUM(i.total) AS billed, COALESCE(SUM(p.paid), 0) AS collected
FROM invoices i
LEFT JOIN paid p ON p.invoice_id = i.id
GROUP BY i.client;

Your task

Larkspur Freight promises its customers a high fill rate: units shipped as a share of units ordered. Order lines are sometimes split across warehouses or shipped short, and account managers want May 2025's figure for every customer. Cancelled orders don't count.

For orders that aren't cancelled, return customer, units_ordered (sum of qty_ordered), units_shipped (sum of qty_shipped from lg_fulfillments, 0 for lines that never shipped) and fill_rate_pct (units_shipped as a % of units_ordered, rounded to 1 decimal). Sort by fill_rate_pct, then customer.

Starting Postgres in your browser…

Checking your access…