Undelivered Shipments Past Their Promised Date

Easy
304.

Undelivered Shipments Past Their Promised Date

EasyDatesBasics

MySQL

The customer-experience desk runs a breach report every morning. On the report date 2024-06-15, a shipment is breached when it is still in_transit, has no delivered_on date, and its promised_date is before 2024-06-15. Shipments returned to origin (rto) are handled elsewhere and never appear.

Return awb, promised_date and days_overdue (the days from the promised date to 2024-06-15) for every breached shipment, ordered by days_overdue descending and then awb ascending.

Tables

Table: Shipment

ColumnTypeValues
awbbigint
destination_cityvarchar
promised_datedate
delivered_ondate
statusenumin_transit, delivered, rto

Primary key: awb.

awb is the 12-digit air waybill number. delivered_on is NULL until the parcel is delivered.

Example

The rows must come back in the order shown.

Shipment

awbdestination_citypromised_datedelivered_onstatus
784512300011Pune2024-06-10NULLin_transit
784512300012Mumbai2024-06-15NULLin_transit
784512300013Delhi2024-06-122024-06-14delivered
784512300014Chennai2024-06-01NULLrto
784512300015Kochi2024-06-14NULLin_transit
784512300016Jaipur2024-06-10NULLin_transit
784512300017Delhi2024-06-20NULLin_transit

Output

awbpromised_datedays_overdue
7845123000112024-06-105
7845123000162024-06-105
7845123000152024-06-141
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.

Undelivered Shipments Past Their Promised Date — SQL Problem

The customer-experience desk runs a breach report every morning.

  • Difficulty: Easy
  • Topics: Dates, Basics
  • Dialect: MySQL
  • Problem: #304

Problem statement

The customer-experience desk runs a breach report every morning. On the report date 2024-06-15, a shipment is breached when it is still in_transit, has no delivered_on date, and its promised_date is before 2024-06-15. Shipments returned to origin (rto) are handled elsewhere and never appear.

Return awb, promised_date and days_overdue (the days from the promised date to 2024-06-15) for every breached shipment, ordered by days_overdue descending and then awb ascending.

Tables

Table: Shipment

ColumnType
awbbigint
destination_cityvarchar
promised_datedate
delivered_ondate
statusenum(in_transit, delivered, rto)

Primary key: awb.

awb is the 12-digit air waybill number. delivered_on is NULL until the parcel is delivered.

Examples

Example 1

Shipment

awbdestination_citypromised_datedelivered_onstatus
784512300011Pune2024-06-10NULLin_transit
784512300012Mumbai2024-06-15NULLin_transit
784512300013Delhi2024-06-122024-06-14delivered
784512300014Chennai2024-06-01NULLrto
784512300015Kochi2024-06-14NULLin_transit
784512300016Jaipur2024-06-10NULLin_transit
784512300017Delhi2024-06-20NULLin_transit

Output

awbpromised_datedays_overdue
7845123000112024-06-105
7845123000162024-06-105
7845123000152024-06-141

How to solve Undelivered Shipments Past Their Promised Date

Breached shipments are the rows that pass three tests: status = 'in_transit' (RTO and delivered parcels are excluded), delivered_on IS NULL (no delivery recorded) and promised_date < '2024-06-15'. The comparison is strict — a parcel promised for the 15th still has the whole day, so it is not overdue on the morning report.

The overdue age is the gap between two dates, DATEDIFF('2024-06-15', promised_date); the later date goes first so the result is positive. TIMESTAMPDIFF(DAY, promised_date, '2024-06-15') takes its arguments the other way round and gives the same number for plain dates.

Testing IS NULL needs IS, never = NULL, which is never true. The order is oldest breach first, and since many parcels share a promised date the AWB number breaks ties. With an index on (status, promised_date) the report reads only the in-transit rows it needs.

Reference solution (MySQL)

SELECT awb, promised_date, DATEDIFF('2024-06-15', promised_date) AS days_overdue
FROM Shipment
WHERE status = 'in_transit'
  AND delivered_on IS NULL
  AND promised_date < '2024-06-15'
ORDER BY days_overdue DESC, awb

Another way

SELECT awb, promised_date, TIMESTAMPDIFF(DAY, promised_date, '2024-06-15') AS days_overdue FROM Shipment WHERE status NOT IN ('delivered', 'rto') AND delivered_on IS NULL AND DATEDIFF('2024-06-15', promised_date) > 0 ORDER BY days_overdue DESC, awb ASC

← Late-Night Ride Requests for the May Safety Audit · Drivers Rated Above the Fleet Average →