Walmart: Each Store's Sales Gap to Its Region's Leader

Medium
1012.

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.

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

ColumnType
store_idint
regionvarchar

Primary key: store_id.

One row per store and the operating region it reports into.

Table: DeptWeekSales

ColumnType
store_idint
week_startdate
departmentvarchar
net_salesint

Primary key (together): store_id, week_start, department.

One row per store, week (starting Monday) and department: net sales in dollars.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Store

store_idregion
1204South
3318South
5260South
4100West
4177West
4200West

DeptWeekSales

store_idweek_startdepartmentnet_sales
12042025-05-05Grocery52000
12042025-05-05Electronics18000
33182025-05-05Grocery61000
52602025-05-05Grocery40000
52602025-05-05Home21000
52602025-04-28Grocery90000
41002025-05-05Grocery30000
41772025-05-05Grocery30000
42002025-05-12Grocery45000

Output

store_idregionnet_salesgap_to_leader
1204South700000
3318South610009000
5260South610009000
4100West300000
4177West300000
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: 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

ColumnType
store_idint
regionvarchar

Primary key: store_id.

One row per store and the operating region it reports into.

Table: DeptWeekSales

ColumnType
store_idint
week_startdate
departmentvarchar
net_salesint

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_idregion
1204South
3318South
5260South
4100West
4177West
4200West

DeptWeekSales

store_idweek_startdepartmentnet_sales
12042025-05-05Grocery52000
12042025-05-05Electronics18000
33182025-05-05Grocery61000
52602025-05-05Grocery40000
52602025-05-05Home21000
52602025-04-28Grocery90000
41002025-05-05Grocery30000
41772025-05-05Grocery30000
42002025-05-12Grocery45000

Output

store_idregionnet_salesgap_to_leader
1204South700000
3318South610009000
5260South610009000
4100West300000
4177West300000

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 t

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