Drill 4 of 12 in Logistics questions
0

Interview Question Bank · COALESCE to a snapshot date · date arithmetic

4. Late shipments and days late

MediumLogisticsQuestion #160

Concept: COALESCE to a snapshot date · date arithmeticshow

Lateness has two cases: delivered after the promise, or not delivered and already past it. COALESCE(delivered_at::date, snapshot_date) handles both in one expression, treating an open shipment as if it lands on the snapshot day, so the same comparison and subtraction work for every row. Use a fixed snapshot date rather than current_date, or the answer changes every day the data sits still.

Example (different data):

SELECT id, due_on,
  COALESCE(returned_on, DATE '2025-01-31') - due_on AS days_overdue
FROM tool_rentals
WHERE COALESCE(returned_on, DATE '2025-01-31') > due_on;

Your task

Larkspur Freight's customer service team wants a list of every late shipment in March, including the ones still on the road. The data is a snapshot taken on 2025-03-31.

A shipment is late if it was delivered on a date after promised_by, or if it is undelivered and promised_by is before 2025-03-31 (one due on 2025-03-31 isn't late yet). Return shipment_id, carrier (name), promised_by, delivered_on (delivered_at's date, NULL if undelivered) and days_late (delivered date, or 2025-03-31 if undelivered, minus promised_by). Sort by days_late descending, then shipment_id.

Starting Postgres in your browser…

Checking your access…