Walmart: Late Store Deliveries by Day of the Week
Walmart's transportation planners suspect certain days of the week are harder for the truck fleet.
- Difficulty: Medium
- Topics: Dates, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #1015
Problem statement
Walmart's transportation planners suspect certain days of the week are harder for the truck fleet. They want, per weekday, how many deliveries were scheduled and how many were late.
A delivery is late when it arrived more than 30 minutes after scheduled_at, or never arrived (arrived_at is NULL). Group deliveries by the weekday of scheduled_at and return day_name (e.g. Monday), deliveries and late_deliveries, for weekdays with at least one delivery, ordered Monday first through Sunday.
A practice scenario set at Walmart: the tables and data are invented, and this is not a question from, or endorsed by, Walmart.
Tables
Table: StoreDelivery
| Column | Type |
|---|---|
| delivery_id | int |
| dc_id | int |
| store_id | int |
| scheduled_at | datetime |
| arrived_at | datetime |
Primary key: delivery_id.
One row per truck delivery from a distribution centre to a store. arrived_at is NULL when the delivery never arrived.
Examples
Example 1
StoreDelivery
| delivery_id | dc_id | store_id | scheduled_at | arrived_at |
|---|---|---|---|---|
| 1 | 6011 | 1204 | 2025-06-02 05:00:00 | 2025-06-02 05:30:00 |
| 2 | 6011 | 3318 | 2025-06-02 06:00:00 | 2025-06-02 06:31:00 |
| 3 | 6011 | 1204 | 2025-06-04 05:00:00 | 2025-06-04 04:45:00 |
| 4 | 6094 | 5260 | 2025-06-04 07:00:00 | NULL |
| 5 | 6094 | 5260 | 2025-06-08 05:00:00 | 2025-06-08 09:00:00 |
| 6 | 6011 | 1204 | 2025-06-09 05:00:00 | 2025-06-09 05:10:00 |
| 7 | 6094 | 3318 | 2025-06-07 23:50:00 | 2025-06-08 00:30:00 |
Output
| day_name | deliveries | late_deliveries |
|---|---|---|
| Monday | 3 | 1 |
| Wednesday | 2 | 1 |
| Saturday | 1 | 1 |
| Sunday | 1 | 1 |
How to solve Walmart: Late Store Deliveries by Day of the Week
The grouping key is the weekday of the scheduled time, and the order must follow the calendar, not the alphabet. DAYNAME gives the label to show, while WEEKDAY (Monday = 0 … Sunday = 6) gives the number to sort by. Since every row in a DAYNAME group has the same WEEKDAY, ordering by MIN(WEEKDAY(scheduled_at)) is a legal aggregate that sorts the groups correctly.
Lateness is a conditional count. A delivery that never arrived has arrived_at NULL, and TIMESTAMPDIFF with a NULL is NULL — it would be counted as on time unless the CASE tests for NULL first. The threshold is strict: arriving exactly 30 minutes after the slot is on time, 31 minutes is late. Early arrivals give a negative difference and are on time.
Note the delivery scheduled at 23:50 on a Saturday belongs to Saturday even though it arrived on Sunday — the weekday comes from scheduled_at. The alternative computes the weekday number, label and late flag per row in a derived table and groups by both the number and the label. One scan either way.
Reference solution (MySQL)
SELECT DAYNAME(scheduled_at) AS day_name,
COUNT(*) AS deliveries,
SUM(CASE WHEN arrived_at IS NULL OR TIMESTAMPDIFF(MINUTE, scheduled_at, arrived_at) > 30 THEN 1 ELSE 0 END) AS late_deliveries
FROM StoreDelivery
GROUP BY DAYNAME(scheduled_at)
ORDER BY MIN(WEEKDAY(scheduled_at))Another way
SELECT day_name, COUNT(*) AS deliveries, SUM(late) AS late_deliveries
FROM (
SELECT WEEKDAY(scheduled_at) AS wd, DAYNAME(scheduled_at) AS day_name,
IF(arrived_at IS NULL, 1, IF(TIMESTAMPDIFF(MINUTE, scheduled_at, arrived_at) > 30, 1, 0)) AS late
FROM StoreDelivery
) t
GROUP BY wd, day_name
ORDER BY wd← Walmart: Item Records Sharing a UPC After Cleanup · Walmart: Did the Rollback Lift Daily Unit Sales? →