Interview Question Bank · First row per group · DISTINCT ON · denominators
8. First-attempt delivery success rate
MediumLogisticsQuestion #164
Concept: First row per group · DISTINCT ON · denominatorsshow
To judge each entity by its first event, reduce the events to one row per entity first: DISTINCT ON (entity) ... ORDER BY entity, event_time keeps the earliest. Order by the timestamp, not the id, because ids record when a row was inserted, not when the event happened. Then aggregate those first rows; counting raw event rows would put retries in the denominator.
Example (different data):
WITH first_try AS ( SELECT DISTINCT ON (student_id) student_id, passed FROM exam_sittings ORDER BY student_id, sat_on ) SELECT ROUND(100.0 * COUNT(*) FILTER (WHERE passed) / COUNT(*), 1) AS first_time_pass_pct FROM first_try;
Your task
Every failed doorstep attempt costs Larkspur Freight a redelivery. Operations wants each carrier's first-attempt success rate. Failed attempts were uploaded from scanners in a later batch, so attempt ids don't follow time order.
For shipments with at least one delivery attempt, find each shipment's first attempt (earliest attempted_at). Per carrier, return carrier (name), shipments (with an attempt), first_attempt_ok (first attempt was 'delivered') and success_pct (rounded to 1 decimal). Sort by success_pct descending, then carrier.
Starting Postgres in your browser…
Checking your access…