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…