Walmart: Late Store Deliveries by Day of the Week

Medium
1015.

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. 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

ColumnType
delivery_idint
dc_idint
store_idint
scheduled_atdatetime
arrived_atdatetime

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.

Example

The rows must come back in the order shown.

StoreDelivery

delivery_iddc_idstore_idscheduled_atarrived_at
1601112042025-06-02 05:00:002025-06-02 05:30:00
2601133182025-06-02 06:00:002025-06-02 06:31:00
3601112042025-06-04 05:00:002025-06-04 04:45:00
4609452602025-06-04 07:00:00NULL
5609452602025-06-08 05:00:002025-06-08 09:00:00
6601112042025-06-09 05:00:002025-06-09 05:10:00
7609433182025-06-07 23:50:002025-06-08 00:30:00

Output

day_namedeliverieslate_deliveries
Monday31
Wednesday21
Saturday11
Sunday11
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.

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

ColumnType
delivery_idint
dc_idint
store_idint
scheduled_atdatetime
arrived_atdatetime

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_iddc_idstore_idscheduled_atarrived_at
1601112042025-06-02 05:00:002025-06-02 05:30:00
2601133182025-06-02 06:00:002025-06-02 06:31:00
3601112042025-06-04 05:00:002025-06-04 04:45:00
4609452602025-06-04 07:00:00NULL
5609452602025-06-08 05:00:002025-06-08 09:00:00
6601112042025-06-09 05:00:002025-06-09 05:10:00
7609433182025-06-07 23:50:002025-06-08 00:30:00

Output

day_namedeliverieslate_deliveries
Monday31
Wednesday21
Saturday11
Sunday11

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? →