Walmart: Each Store's Sales Gap to Its Region's Leader
A Walmart regional general manager reviews the week starting 2025-05-05 by asking each store manager how far they sit behind the best store in the region.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #1012
Problem statement
A Walmart regional general manager reviews the week starting 2025-05-05 by asking each store manager how far they sit behind the best store in the region.
A store's weekly sales are the sum of net_sales over all its department rows for week_start = '2025-05-05'. For every store with at least one row that week, return store_id, region, net_sales (that total) and gap_to_leader — the highest store total in the same region that week minus this store's total (0 for the leader, and for every store tied with it). Stores with no rows that week are left out. Return the rows in any order.
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: Store
| Column | Type |
|---|---|
| store_id | int |
| region | varchar |
Primary key: store_id.
One row per store and the operating region it reports into.
Table: DeptWeekSales
| Column | Type |
|---|---|
| store_id | int |
| week_start | date |
| department | varchar |
| net_sales | int |
Primary key: store_id, week_start, department.
One row per store, week (starting Monday) and department: net sales in dollars.
Examples
Example 1
Store
| store_id | region |
|---|---|
| 1204 | South |
| 3318 | South |
| 5260 | South |
| 4100 | West |
| 4177 | West |
| 4200 | West |
DeptWeekSales
| store_id | week_start | department | net_sales |
|---|---|---|---|
| 1204 | 2025-05-05 | Grocery | 52000 |
| 1204 | 2025-05-05 | Electronics | 18000 |
| 3318 | 2025-05-05 | Grocery | 61000 |
| 5260 | 2025-05-05 | Grocery | 40000 |
| 5260 | 2025-05-05 | Home | 21000 |
| 5260 | 2025-04-28 | Grocery | 90000 |
| 4100 | 2025-05-05 | Grocery | 30000 |
| 4177 | 2025-05-05 | Grocery | 30000 |
| 4200 | 2025-05-12 | Grocery | 45000 |
Output
| store_id | region | net_sales | gap_to_leader |
|---|---|---|---|
| 1204 | South | 70000 | 0 |
| 3318 | South | 61000 | 9000 |
| 5260 | South | 61000 | 9000 |
| 4100 | West | 30000 | 0 |
| 4177 | West | 30000 | 0 |
How to solve Walmart: Each Store's Sales Gap to Its Region's Leader
Two levels of aggregation are stacked here. The data is per department, so the first step is a store's weekly total: join stores to their rows, keep the week of 5 May, and GROUP BY store. A store whose rows are all from other weeks never forms a group, which is what the statement asks for.
The second step compares each store with the best total in its region. A GROUP BY region would collapse the stores into one row per region; a window function computes the regional maximum without collapsing, so MAX(net_sales) OVER (PARTITION BY region) sits next to every store's own total and the gap is a subtraction. The leader gets 0, and so does any store tied with it — no tie-breaking is needed because nothing is ranked.
Window functions are evaluated after GROUP BY, which is why the totals are computed in a CTE first. The alternative computes each region's maximum in a second CTE and joins it back — the classic pre-window pattern. Both are a sort or hash per region on top of one scan.
Reference solution (MySQL)
WITH t AS (
SELECT s.store_id, s.region, SUM(d.net_sales) AS net_sales
FROM Store s
JOIN DeptWeekSales d ON d.store_id = s.store_id
WHERE d.week_start = '2025-05-05'
GROUP BY s.store_id, s.region
)
SELECT store_id, region, net_sales,
MAX(net_sales) OVER (PARTITION BY region) - net_sales AS gap_to_leader
FROM tAnother way
WITH t AS (
SELECT s.store_id, s.region, SUM(d.net_sales) AS net_sales
FROM Store s JOIN DeptWeekSales d ON d.store_id = s.store_id
WHERE d.week_start = '2025-05-05'
GROUP BY s.store_id, s.region
), best AS (
SELECT region, MAX(net_sales) AS top_sales FROM t GROUP BY region
)
SELECT t.store_id, t.region, t.net_sales, b.top_sales - t.net_sales AS gap_to_leader
FROM t JOIN best b ON b.region = t.region← Walmart: Close-Then-Open Shifts With Under Ten Hours' Rest · Walmart: Repeat Receiptless Returns Across Stores →