Drill 3 of 12 in Food delivery questions
0

Interview Question Bank · Timestamp comparison · FILTER · strict boundary

3. Late deliveries against the promised time

EasyFood deliveryQuestion #231

Concept: Timestamp comparison · FILTER · strict boundaryshow

Timestamps compare like numbers, so 'late' is just delivered_at > promised_at. A rate needs the right denominator: only the rows where the event could happen at all. Decide whether the boundary counts: arriving on the promised minute is on time, so late is strictly greater.

Example (different data):

SELECT warehouse,
  COUNT(*) AS shipped,
  ROUND(100.0 * COUNT(*) FILTER (WHERE shipped_at > ship_by) / COUNT(*), 1) AS late_pct
FROM shipments
WHERE shipped_at IS NOT NULL
GROUP BY warehouse;

Your task

At checkout Brightbowl promises each customer an arrival time (promised_at). The city managers want to know how often each city breaks that promise.

Using delivered orders only, return city (the restaurant's city), delivered_orders, late_orders (delivered after promised_at; arriving exactly at promised_at is on time) and late_pct (late as a % of delivered orders, ROUND(..., 1)). Sort by late_pct descending, then city.

Starting Postgres in your browser…

Checking your access…