Walmart: Grocery vs General Merchandise Sales per Store
Walmart's store operations team compares how much of each store's March business is food against everything else on the floor.
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #1001
Problem statement
Walmart's store operations team compares how much of each store's March business is food against everything else on the floor.
Departments Grocery, Produce, Dairy and Bakery are grocery; every other department is general merchandise. Looking only at sales dated in March 2025, return one row per store that has at least one March row: store_id, grocery_sales and general_merch_sales (each the sum of net_sales, 0 when the store sold nothing in that group). Order the rows by store_id.
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: DeptDailySales
| Column | Type |
|---|---|
| store_id | int |
| sale_date | date |
| department | varchar |
| net_sales | int |
Primary key: store_id, sale_date, department.
One row per store, day and department: the day's net sales in dollars after returns.
Examples
Example 1
DeptDailySales
| store_id | sale_date | department | net_sales |
|---|---|---|---|
| 100 | 2025-03-01 | Grocery | 5200 |
| 100 | 2025-03-01 | Electronics | 3100 |
| 100 | 2025-03-31 | Produce | 1800 |
| 100 | 2025-04-01 | Grocery | 9900 |
| 205 | 2025-03-14 | Toys | 700 |
| 205 | 2025-03-15 | Apparel | 950 |
| 318 | 2025-02-28 | Dairy | 1200 |
| 318 | 2025-03-02 | Bakery | 400 |
Output
| store_id | grocery_sales | general_merch_sales |
|---|---|---|
| 100 | 7000 | 3100 |
| 205 | 0 | 1650 |
| 318 | 400 | 0 |
How to solve Walmart: Grocery vs General Merchandise Sales per Store
The output pivots one column of the data — the department — into two totals per store. That is conditional aggregation: group by store, and inside each group add up net_sales twice, once counting only grocery rows and once counting only the rest.
SUM(CASE WHEN department IN (…) THEN net_sales ELSE 0 END) adds a row's sales when it is a grocery department and 0 otherwise. Using ELSE 0 rather than leaving the ELSE out matters: a store that sold no general merchandise in March would otherwise get NULL, and the statement asks for 0. General merchandise is just the complement — everything not in the list — so its CASE flips the branches.
The date filter runs in WHERE, so February and April rows never reach a group; a store whose only rows are outside March forms no group and is left out, as asked. BETWEEN '2025-03-01' AND '2025-03-31' keeps both the first and the last day. The alternative uses IF, takes the general-merchandise figure as the total minus grocery, and filters with YEAR/MONTH. One scan either way.
Reference solution (MySQL)
SELECT store_id,
SUM(CASE WHEN department IN ('Grocery', 'Produce', 'Dairy', 'Bakery') THEN net_sales ELSE 0 END) AS grocery_sales,
SUM(CASE WHEN department IN ('Grocery', 'Produce', 'Dairy', 'Bakery') THEN 0 ELSE net_sales END) AS general_merch_sales
FROM DeptDailySales
WHERE sale_date BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY store_id
ORDER BY store_idAnother way
SELECT store_id,
SUM(IF(department IN ('Grocery', 'Produce', 'Dairy', 'Bakery'), net_sales, 0)) AS grocery_sales,
SUM(net_sales) - SUM(IF(department IN ('Grocery', 'Produce', 'Dairy', 'Bakery'), net_sales, 0)) AS general_merch_sales
FROM DeptDailySales
WHERE YEAR(sale_date) = 2025 AND MONTH(sale_date) = 3
GROUP BY store_id ORDER BY store_id← Walmart: Active Walmart+ Members on 30 June by Plan · Walmart: Associate Shifts Clocked Over Ten Hours →