Delivery SLA Breaches by Postal Zone

Medium
110.

Delivery SLA Breaches by Postal Zone

The logistics team tracks how often couriers miss the promised delivery time. The first digit of an Indian PIN code is its postal zone (1 = Delhi and the north, 4 = the west, 5 = Andhra/Telangana/Karnataka, and so on). A delivered shipment breaches its SLA when the days from shipped_on to delivered_on are more than promised_days. Shipments not yet delivered are ignored.

Return one row per zone with at least one delivered shipment: zone (the first digit), delivered and breaches. Order by breaches descending, then zone ascending.

Tables

Table: shipments

ColumnType
shipment_idint
order_idint
pincodechar
shipped_ondate
promised_daysint
delivered_ondate

Primary key: shipment_id.

One row per shipment; pincode is the six-digit delivery PIN code as text. delivered_on is NULL while in transit.

Example

The rows must come back in the order shown.

shipments

shipment_idorder_idpincodeshipped_onpromised_daysdelivered_on
191015600342024-10-0122024-10-03
291025601022024-10-0122024-10-04
391034000762024-10-0232024-10-07
491044110142024-10-0232024-10-06
591051100172024-10-0342024-10-05
691065000812024-10-032NULL
791076000422024-10-0432024-10-08
891081220022024-10-0442024-10-08

Output

zonedeliveredbreaches
422
521
611
120
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.

Delivery SLA Breaches by Postal Zone — SQL Dates Problem

The logistics team tracks how often couriers miss the promised delivery time.

  • Difficulty: Medium
  • Topics: Dates, Strings, Aggregation
  • Dialect: MySQL
  • Problem: #110

Problem statement

The logistics team tracks how often couriers miss the promised delivery time. The first digit of an Indian PIN code is its postal zone (1 = Delhi and the north, 4 = the west, 5 = Andhra/Telangana/Karnataka, and so on). A delivered shipment breaches its SLA when the days from shipped_on to delivered_on are more than promised_days. Shipments not yet delivered are ignored.

Return one row per zone with at least one delivered shipment: zone (the first digit), delivered and breaches. Order by breaches descending, then zone ascending.

Tables

Table: shipments

ColumnType
shipment_idint
order_idint
pincodechar
shipped_ondate
promised_daysint
delivered_ondate

Primary key: shipment_id.

One row per shipment; pincode is the six-digit delivery PIN code as text. delivered_on is NULL while in transit.

Examples

Example 1

shipments

shipment_idorder_idpincodeshipped_onpromised_daysdelivered_on
191015600342024-10-0122024-10-03
291025601022024-10-0122024-10-04
391034000762024-10-0232024-10-07
491044110142024-10-0232024-10-06
591051100172024-10-0342024-10-05
691065000812024-10-032NULL
791076000422024-10-0432024-10-08
891081220022024-10-0442024-10-08

Output

zonedeliveredbreaches
422
521
611
120

How to solve Delivery SLA Breaches by Postal Zone

The zone is a string operation on the PIN code — its first character, LEFT(pincode, 1) — and it is the grouping key. Because PIN codes are stored as text, there is no arithmetic involved, and a leading digit is never lost the way it could be if the code were a number.

A shipment breaches when DATEDIFF(delivered_on, shipped_on) > promised_days; delivering on exactly the promised day is on time (shipment 4 in the example takes 4 days against 3 promised and breaches; shipment 1 takes 2 against 2 and does not). The breach count is a conditional SUM, and the delivered count is the group size once the in-transit rows (NULL delivered_on) are filtered out in WHERE. A zone whose only shipment is still in transit has no group and is not listed.

The alternative states the SLA as a date comparison: late when delivered_on is after shipped_on plus the promised days. The two are the same rule. One pass over the shipments, then a sort of at most ten zones.

Reference solution (MySQL)

SELECT LEFT(pincode, 1) AS zone,
       COUNT(*) AS delivered,
       SUM(CASE WHEN DATEDIFF(delivered_on, shipped_on) > promised_days THEN 1 ELSE 0 END) AS breaches
FROM shipments
WHERE delivered_on IS NOT NULL
GROUP BY LEFT(pincode, 1)
ORDER BY breaches DESC, zone

Another way

SELECT zone, COUNT(*) AS delivered, SUM(late) AS breaches
FROM (SELECT SUBSTRING(pincode, 1, 1) AS zone, IF(delivered_on > DATE_ADD(shipped_on, INTERVAL promised_days DAY), 1, 0) AS late
      FROM shipments WHERE delivered_on IS NOT NULL) t
GROUP BY zone
ORDER BY breaches DESC, zone

← Customers Whose First Order Used a Coupon · Flash Sale Units Sold Inside the Sale Window →