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
| Column | Type |
|---|---|
| parcel_id | int |
| hub_code | varchar |
| arrived_on | date |
| dispatched_on | date |
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_id | hub_code | arrived_on | dispatched_on |
|---|---|---|---|
| 1 | BLR-HSK | 2024-06-29 | 2024-07-01 |
| 2 | BLR-HSK | 2024-07-01 | 2024-07-01 |
| 3 | BLR-HSK | 2024-07-02 | NULL |
| 4 | BLR-HSK | 2024-07-03 | 2024-07-04 |
| 5 | BLR-HSK | 2024-07-05 | 2024-07-06 |
| 6 | BLR-HSK | 2024-07-11 | NULL |
Output
| day | backlog | ageing |
|---|---|---|
| 2024-07-01 | 0 | 0 |
| 2024-07-02 | 1 | 0 |
| 2024-07-03 | 2 | 0 |
| 2024-07-04 | 1 | 0 |
| 2024-07-05 | 2 | 1 |
| 2024-07-06 | 1 | 1 |
| 2024-07-07 | 1 | 1 |
| 2024-07-08 | 1 | 1 |
| 2024-07-09 | 1 | 1 |
| 2024-07-10 | 1 | 1 |
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.dayAnother 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 →