Couriers Who Missed the Delivery SLA on Over 20% of May Drops

Medium
307.

Couriers Who Missed the Delivery SLA on Over 20% of May Drops

Each delivery carries a promised_by time; a delivery is late when delivered_at is after promised_by. The last-mile manager reviews couriers whose late share in May 2024 (deliveries whose delivered_at falls in May 2024) is strictly more than 20%. Failed deliveries have no delivered_at and are ignored.

Return courier_id, name, deliveries (May deliveries), late_deliveries and late_pct (late ÷ deliveries × 100, rounded to 2 decimals). Order by late_pct descending, then courier_id ascending.

Tables

Table: Courier

ColumnType
courier_idint
namevarchar
hubvarchar

Primary key: courier_id.

One row per last-mile delivery associate.

Table: Delivery

ColumnTypeValues
delivery_idint
courier_idint
promised_bydatetime
delivered_atdatetime
statusenumdelivered, failed

Primary key: delivery_id.

delivered_at is NULL for a failed attempt. A delivery exactly at promised_by is on time.

Example

The rows must come back in the order shown.

Courier

courier_idnamehub
1MaheshHSR Layout
2AnwarAndheri East
3SelviVelachery

Delivery

delivery_idcourier_idpromised_bydelivered_atstatus
112024-05-02 18:00:002024-05-02 18:40:00delivered
212024-05-03 14:00:002024-05-03 13:10:00delivered
312024-05-04 12:00:002024-05-04 12:00:00delivered
412024-05-05 20:00:00NULLfailed
522024-05-10 11:00:002024-05-10 10:30:00delivered
622024-05-11 11:00:002024-05-11 12:15:00delivered
722024-05-12 11:00:002024-05-12 10:00:00delivered
822024-05-13 11:00:002024-05-13 10:45:00delivered
922024-05-14 11:00:002024-05-14 10:50:00delivered
1032024-04-30 21:00:002024-05-01 09:30:00delivered
1 more row

Output

courier_idnamedeliverieslate_deliverieslate_pct
3Selvi11100
1Mahesh3133.33
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Couriers Who Missed the Delivery SLA on Over 20% of May Drops

Each delivery carries a promised_by time; a delivery is late when delivered_at is after promised_by.

  • Difficulty: Medium
  • Topics: Aggregation, Dates, Joins
  • Dialect: MySQL
  • Problem: #307

Problem statement

Each delivery carries a promised_by time; a delivery is late when delivered_at is after promised_by. The last-mile manager reviews couriers whose late share in May 2024 (deliveries whose delivered_at falls in May 2024) is strictly more than 20%. Failed deliveries have no delivered_at and are ignored.

Return courier_id, name, deliveries (May deliveries), late_deliveries and late_pct (late ÷ deliveries × 100, rounded to 2 decimals). Order by late_pct descending, then courier_id ascending.

Tables

Table: Courier

ColumnType
courier_idint
namevarchar
hubvarchar

Primary key: courier_id.

One row per last-mile delivery associate.

Table: Delivery

ColumnType
delivery_idint
courier_idint
promised_bydatetime
delivered_atdatetime
statusenum(delivered, failed)

Primary key: delivery_id.

delivered_at is NULL for a failed attempt. A delivery exactly at promised_by is on time.

Examples

Example 1

Courier

courier_idnamehub
1MaheshHSR Layout
2AnwarAndheri East
3SelviVelachery

Delivery

delivery_idcourier_idpromised_bydelivered_atstatus
112024-05-02 18:00:002024-05-02 18:40:00delivered
212024-05-03 14:00:002024-05-03 13:10:00delivered
312024-05-04 12:00:002024-05-04 12:00:00delivered
412024-05-05 20:00:00NULLfailed
522024-05-10 11:00:002024-05-10 10:30:00delivered
622024-05-11 11:00:002024-05-11 12:15:00delivered
722024-05-12 11:00:002024-05-12 10:00:00delivered
822024-05-13 11:00:002024-05-13 10:45:00delivered
922024-05-14 11:00:002024-05-14 10:50:00delivered
1032024-04-30 21:00:002024-05-01 09:30:00delivered
1132024-05-31 22:00:002024-06-01 08:00:00delivered

Output

courier_idnamedeliverieslate_deliverieslate_pct
3Selvi11100
1Mahesh3133.33

How to solve Couriers Who Missed the Delivery SLA on Over 20% of May Drops

First decide which rows are in scope: deliveries whose delivered_at falls in May 2024, written as >= '2024-05-01' and < '2024-06-01'. That one filter also removes failed attempts, because a NULL delivered_at fails every comparison. A parcel promised on 30 April but dropped on 1 May is a May delivery; one dropped on 1 June is not.

Then group per courier and compute two numbers in one pass: COUNT(*) for the deliveries and SUM(CASE WHEN delivered_at > promised_by THEN 1 ELSE 0 END) for the late ones. A drop exactly at the promised time is on time, so the comparison is strict.

The threshold compares aggregates, so it is a HAVING. Writing it as 5 * late > deliveries keeps it in integers — no rounding can push 20.0000 % over the line — while late / deliveries > 0.2 is equivalent in exact arithmetic. The percentage itself is rounded to two decimals for display. The cost is one scan of the month's deliveries plus a join to the small courier table.

Reference solution (MySQL)

SELECT c.courier_id, c.name,
       COUNT(*) AS deliveries,
       SUM(CASE WHEN d.delivered_at > d.promised_by THEN 1 ELSE 0 END) AS late_deliveries,
       ROUND(100 * SUM(CASE WHEN d.delivered_at > d.promised_by THEN 1 ELSE 0 END) / COUNT(*), 2) AS late_pct
FROM Courier c
JOIN Delivery d ON d.courier_id = c.courier_id
WHERE d.delivered_at >= '2024-05-01' AND d.delivered_at < '2024-06-01'
GROUP BY c.courier_id, c.name
HAVING 5 * SUM(CASE WHEN d.delivered_at > d.promised_by THEN 1 ELSE 0 END) > COUNT(*)
ORDER BY late_pct DESC, c.courier_id

Another way

WITH may AS (
  SELECT courier_id, COUNT(*) AS deliveries, SUM(IF(delivered_at > promised_by, 1, 0)) AS late_deliveries
  FROM Delivery
  WHERE status = 'delivered' AND DATE_FORMAT(delivered_at, '%Y-%m') = '2024-05'
  GROUP BY courier_id
)
SELECT c.courier_id, c.name, m.deliveries, m.late_deliveries, ROUND(100 * m.late_deliveries / m.deliveries, 2) AS late_pct
FROM may m JOIN Courier c ON c.courier_id = m.courier_id
WHERE m.late_deliveries / m.deliveries > 0.2
ORDER BY late_pct DESC, c.courier_id

← Dispatch Offer Acceptance Rate per Driver · Top-Earning Driver in Each City for Q1 2024 →