Walmart: Grocery vs General Merchandise Sales per Store

Easy
1001.

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.

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

ColumnType
store_idint
sale_datedate
departmentvarchar
net_salesint

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

One row per store, day and department: the day's net sales in dollars after returns.

Example

The rows must come back in the order shown.

DeptDailySales

store_idsale_datedepartmentnet_sales
1002025-03-01Grocery5200
1002025-03-01Electronics3100
1002025-03-31Produce1800
1002025-04-01Grocery9900
2052025-03-14Toys700
2052025-03-15Apparel950
3182025-02-28Dairy1200
3182025-03-02Bakery400

Output

store_idgrocery_salesgeneral_merch_sales
10070003100
20501650
3184000
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: 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

ColumnType
store_idint
sale_datedate
departmentvarchar
net_salesint

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_idsale_datedepartmentnet_sales
1002025-03-01Grocery5200
1002025-03-01Electronics3100
1002025-03-31Produce1800
1002025-04-01Grocery9900
2052025-03-14Toys700
2052025-03-15Apparel950
3182025-02-28Dairy1200
3182025-03-02Bakery400

Output

store_idgrocery_salesgeneral_merch_sales
10070003100
20501650
3184000

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_id

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