Walmart: Pickup Slot Fill Rate by Hour of Day

Medium
1010.

Walmart: Pickup Slot Fill Rate by Hour of Day

The online grocery planning team at Walmart sizes pickup capacity by hour of day: hours that fill up need more associates picking, hours that sit empty can lose windows.

For every start_hour that has at least one pickup window, return start_hour, capacity (the total capacity of the windows at that hour), booked (the number of their bookings with status booked; cancelled ones do not count) and fill_pct (booked as a percentage of capacity, rounded to 2 decimals). Hours with windows but no bookings show booked 0. Order by start_hour.

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: PickupWindow

ColumnType
window_idint
store_idint
window_datedate
start_hourint
capacityint

Primary key: window_id.

One row per pickup window a store offered; capacity is how many orders it can hand over.

Table: PickupBooking

ColumnTypeValues
booking_idint
window_idint
customer_idint
statusenumbooked, cancelled

Primary key: booking_id.

One row per customer booking of a window.

Example

The rows must come back in the order shown.

PickupWindow

window_idstore_idwindow_datestart_hourcapacity
112042025-04-1294
233182025-04-1292
312042025-04-12125
412042025-04-13123
533182025-04-12176

PickupBooking

booking_idwindow_idcustomer_idstatus
100171booked
101172booked
102173cancelled
103274booked
104375booked
105576cancelled
106277booked

Output

start_hourcapacitybookedfill_pct
96466.67
128112.5
17600
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: Pickup Slot Fill Rate by Hour of Day — SQL Problem

The online grocery planning team at Walmart sizes pickup capacity by hour of day: hours that fill up need more associates picking, hours that sit empty can…

  • Difficulty: Medium
  • Topics: Joins, Aggregation, Subqueries
  • Dialect: MySQL
  • Problem: #1010

Problem statement

The online grocery planning team at Walmart sizes pickup capacity by hour of day: hours that fill up need more associates picking, hours that sit empty can lose windows.

For every start_hour that has at least one pickup window, return start_hour, capacity (the total capacity of the windows at that hour), booked (the number of their bookings with status booked; cancelled ones do not count) and fill_pct (booked as a percentage of capacity, rounded to 2 decimals). Hours with windows but no bookings show booked 0. Order by start_hour.

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: PickupWindow

ColumnType
window_idint
store_idint
window_datedate
start_hourint
capacityint

Primary key: window_id.

One row per pickup window a store offered; capacity is how many orders it can hand over.

Table: PickupBooking

ColumnType
booking_idint
window_idint
customer_idint
statusenum(booked, cancelled)

Primary key: booking_id.

One row per customer booking of a window.

Examples

Example 1

PickupWindow

window_idstore_idwindow_datestart_hourcapacity
112042025-04-1294
233182025-04-1292
312042025-04-12125
412042025-04-13123
533182025-04-12176

PickupBooking

booking_idwindow_idcustomer_idstatus
100171booked
101172booked
102173cancelled
103274booked
104375booked
105576cancelled
106277booked

Output

start_hourcapacitybookedfill_pct
96466.67
128112.5
17600

How to solve Walmart: Pickup Slot Fill Rate by Hour of Day

The trap here is join fan-out. If you join windows to bookings and group by hour, a window with three bookings appears three times, and SUM(capacity) adds its capacity three times — the fill rate comes out far too low. A window with no bookings disappears altogether under an inner join.

The fix is to aggregate each side at the right grain before combining them. One CTE sums capacity per hour straight from PickupWindow, where every window is one row. Another counts live bookings per hour by joining bookings to their window just to learn the hour. The final LEFT JOIN from capacity to bookings keeps every hour that has a window, and COALESCE(booked, 0) turns a missing count into 0.

Multiply by 100 before dividing so the percentage keeps its decimals, then round to two places. The alternative avoids the fan-out differently: a correlated subquery counts the bookings of each window, so each window is still exactly one row when the outer query groups by hour.

Reference solution (MySQL)

WITH cap AS (
  SELECT start_hour, SUM(capacity) AS capacity
  FROM PickupWindow
  GROUP BY start_hour
), bk AS (
  SELECT w.start_hour, COUNT(*) AS booked
  FROM PickupBooking b
  JOIN PickupWindow w ON w.window_id = b.window_id
  WHERE b.status = 'booked'
  GROUP BY w.start_hour
)
SELECT c.start_hour, c.capacity, COALESCE(bk.booked, 0) AS booked,
       ROUND(100 * COALESCE(bk.booked, 0) / c.capacity, 2) AS fill_pct
FROM cap c
LEFT JOIN bk ON bk.start_hour = c.start_hour
ORDER BY c.start_hour

Another way

SELECT start_hour, SUM(capacity) AS capacity, SUM(n) AS booked, ROUND(100 * SUM(n) / SUM(capacity), 2) AS fill_pct
FROM (
  SELECT w.start_hour, w.capacity,
         (SELECT COUNT(*) FROM PickupBooking b WHERE b.window_id = w.window_id AND b.status = 'booked') AS n
  FROM PickupWindow w
) t
GROUP BY start_hour
ORDER BY start_hour

← Walmart: Substitutions Made Against the Customer's Wishes · Walmart: Close-Then-Open Shifts With Under Ten Hours' Rest →