Drill 8 of 12 in Logistics questions
0

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…