Daily Sorting-Hub Backlog Including Days With None

Hard
322.

Daily Sorting-Hub Backlog Including Days With None

A parcel is in the hub's backlog at the end of a day when it arrived on or before that day and had not been dispatched by then — dispatched_on is NULL, or later than that day. A parcel dispatched on the day it arrived is never in the backlog.

For every day from 2024-07-01 to 2024-07-10 (all ten days, even when the backlog is 0), return day, backlog (parcels in the backlog at the end of the day) and ageing (backlog parcels that arrived more than 2 days before that day). Order by day.

Tables

Table: HubParcel

ColumnType
parcel_idint
hub_codevarchar
arrived_ondate
dispatched_ondate

Primary key: parcel_id.

One row per parcel inbound at the sorting hub. dispatched_on is NULL while the parcel is still there.

Example

The rows must come back in the order shown.

HubParcel

parcel_idhub_codearrived_ondispatched_on
1BLR-HSK2024-06-292024-07-01
2BLR-HSK2024-07-012024-07-01
3BLR-HSK2024-07-02NULL
4BLR-HSK2024-07-032024-07-04
5BLR-HSK2024-07-052024-07-06
6BLR-HSK2024-07-11NULL

Output

daybacklogageing
2024-07-0100
2024-07-0210
2024-07-0320
2024-07-0410
2024-07-0521
2024-07-0611
2024-07-0711
2024-07-0811
2024-07-0911
2024-07-1011
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.

Daily Sorting-Hub Backlog Including Days With None

A parcel is in the hub's backlog at the end of a day when it arrived on or before that day and had not been dispatched by then — dispatched_on is NULL, or…

  • Difficulty: Hard
  • Topics: Dates, Joins, Subqueries
  • Dialect: MySQL
  • Problem: #322

Problem statement

A parcel is in the hub's backlog at the end of a day when it arrived on or before that day and had not been dispatched by then — dispatched_on is NULL, or later than that day. A parcel dispatched on the day it arrived is never in the backlog.

For every day from 2024-07-01 to 2024-07-10 (all ten days, even when the backlog is 0), return day, backlog (parcels in the backlog at the end of the day) and ageing (backlog parcels that arrived more than 2 days before that day). Order by day.

Tables

Table: HubParcel

ColumnType
parcel_idint
hub_codevarchar
arrived_ondate
dispatched_ondate

Primary key: parcel_id.

One row per parcel inbound at the sorting hub. dispatched_on is NULL while the parcel is still there.

Examples

Example 1

HubParcel

parcel_idhub_codearrived_ondispatched_on
1BLR-HSK2024-06-292024-07-01
2BLR-HSK2024-07-012024-07-01
3BLR-HSK2024-07-02NULL
4BLR-HSK2024-07-032024-07-04
5BLR-HSK2024-07-052024-07-06
6BLR-HSK2024-07-11NULL

Output

daybacklogageing
2024-07-0100
2024-07-0210
2024-07-0320
2024-07-0410
2024-07-0521
2024-07-0611
2024-07-0711
2024-07-0811
2024-07-0911
2024-07-1011

How to solve Daily Sorting-Hub Backlog Including Days With None

The output must have a row for every day, but a day with an empty hub has no data to group — so generate the calendar. A recursive CTE starts from CAST('2024-07-01' AS DATE) and adds DATE_ADD(day, INTERVAL 1 DAY) until the 10th: ten rows, bounded by the WHERE in the recursive step.

Then LEFT JOIN parcels to days with the stay as the join condition: arrived on or before the day, and either never dispatched (dispatched_on IS NULL) or dispatched after it. A same-day dispatch fails dispatched_on > day on its only candidate day, so it never counts. COUNT(p.parcel_id) gives 0 for a day nothing matched, and ageing adds a CASE on DATEDIFF(day, arrived_on) > 2; on an unmatched day that CASE sees NULL and adds 0.

The alternative counts with two correlated subqueries per day and replaces the NULL test with COALESCE(dispatched_on, '9999-12-31') — a far-future sentinel that is "after" every day. It also shows a counter column to stop recursion. Either way the cost is ten passes (or one join) over the parcels.

Reference solution (MySQL)

WITH RECURSIVE days AS (
  SELECT CAST('2024-07-01' AS DATE) AS day
  UNION ALL
  SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM days WHERE day < '2024-07-10'
)
SELECT d.day,
       COUNT(p.parcel_id) AS backlog,
       SUM(CASE WHEN DATEDIFF(d.day, p.arrived_on) > 2 THEN 1 ELSE 0 END) AS ageing
FROM days d
LEFT JOIN HubParcel p
  ON p.arrived_on <= d.day
 AND (p.dispatched_on IS NULL OR p.dispatched_on > d.day)
GROUP BY d.day
ORDER BY d.day

Another way

WITH RECURSIVE days (n, day) AS (
  SELECT 1, CAST('2024-07-01' AS DATE)
  UNION ALL
  SELECT n + 1, DATE_ADD(day, INTERVAL 1 DAY) FROM days WHERE n < 10
)
SELECT day,
       (SELECT COUNT(*) FROM HubParcel p WHERE p.arrived_on <= d.day AND COALESCE(p.dispatched_on, '9999-12-31') > d.day) AS backlog,
       (SELECT COUNT(*) FROM HubParcel p WHERE p.arrived_on < DATE_SUB(d.day, INTERVAL 2 DAY) AND COALESCE(p.dispatched_on, '9999-12-31') > d.day) AS ageing
FROM days d
ORDER BY day

← Double-Booked Self-Drive Rental Cars · Urgent Support Tickets Waiting More Than a Day →