Late Deliveries by Carrier
A large online store hands its parcels to several regional carriers. Every shipment carries the date the store promised the shopper, and the logistics team renegotiates contracts with whichever carrier keeps breaking those promises. They want one report: how often each carrier delivers late.
Tables
carriers
carrier_id: the carrier's id.name: the carrier's name, unique.
shipments
shipment_id: the shipment's id.carrier_id: the carrier that has the parcel.shipped_at: the day the parcel left the warehouse.promised_date: the delivery day the shopper was promised.delivered_at: the day it was delivered, orNULLwhile it is still in transit.
Task
For every carrier, report how many of its shipments have been delivered, how many of those arrived late, and the share of delivered shipments that were late, as a percentage.
- A shipment is late when
delivered_atis afterpromised_date. Delivered on the promised day is on time. - Shipments still in transit are not counted at all: they are neither delivered nor late yet.
- A carrier with nothing delivered still appears, with zero counts and no percentage.
Example
In the sample, Swift Parcel has four shipments: three delivered (one of them a day late) and one still in transit. Its row is Swift Parcel | 3 | 1 | 33.3. Northline Freight delivered three, two of them late, and comes first with 66.7.
| carrier | delivered | late | late_pct |
|---|---|---|---|
| Northline Freight | 3 | 2 | 66.7 |
| Swift Parcel | 3 | 1 | 33.3 |
| BlueBox Express | 3 | 0 | 0 |
Submitting also runs your answer against 3 hidden datasets, each built around an edge case: NULLs, ties, empty tables. A failure names the case without showing its data.
Follow-up: The contracts team now wants the same report per ISO week, and a flag on any carrier whose late rate worsened three weeks in a row. Which window function would you reach for?
- Return the columns `carrier` (the carrier's `name`), `delivered`, `late` and `late_pct`, in that order. - `late_pct` is `100 * late / delivered`, rounded to one decimal place. It is `NULL` when `delivered` is `0`. - Every carrier appears exactly once, including carriers with no shipments. - Sort by `late_pct` from highest to lowest, with `NULL` last, then by `carrier` alphabetically.
- Views
- 3