Walmart: Did the Rollback Lift Daily Unit Sales?
Walmart's merchandising analysts judge each rollback by comparing an item's sales rate while the rollback ran with its rate in the week just before it.
- Difficulty: Hard
- Topics: Joins, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #1016
Problem statement
Walmart's merchandising analysts judge each rollback by comparing an item's sales rate while the rollback ran with its rate in the week just before it.
For every rollback, compute at its store and item: units_before, the units sold in the 7 days before start_date (from start_date - 7 to start_date - 1), and units_during, the units sold from start_date to end_date inclusive. A day with no sales row sold 0 units. The rollback lifted sales when its average units per day during the rollback is strictly higher than the average per day over the 7 days before. Return rollback_id, units_before, units_during and lifted ('yes' or 'no') for every rollback, 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: ItemRollback
| Column | Type |
|---|---|
| rollback_id | int |
| store_id | int |
| item_id | int |
| start_date | date |
| end_date | date |
Primary key: rollback_id.
One row per rollback of one item at one store; both dates are inclusive and end_date >= start_date.
Table: DailyItemSales
| Column | Type |
|---|---|
| store_id | int |
| item_id | int |
| sale_date | date |
| units | int |
Primary key: store_id, item_id, sale_date.
Units of the item sold at the store that day. Days with no sales have no row.
Examples
Example 1
ItemRollback
| rollback_id | store_id | item_id | start_date | end_date |
|---|---|---|---|---|
| 1 | 1204 | 801 | 2025-05-08 | 2025-05-11 |
| 2 | 1204 | 802 | 2025-05-15 | 2025-05-21 |
| 3 | 1204 | 803 | 2025-05-20 | 2025-05-20 |
| 4 | 3318 | 801 | 2025-05-08 | 2025-05-11 |
DailyItemSales
| store_id | item_id | sale_date | units |
|---|---|---|---|
| 1204 | 801 | 2025-04-30 | 20 |
| 1204 | 801 | 2025-05-01 | 5 |
| 1204 | 801 | 2025-05-03 | 4 |
| 1204 | 801 | 2025-05-07 | 3 |
| 1204 | 801 | 2025-05-08 | 6 |
| 1204 | 801 | 2025-05-10 | 5 |
| 1204 | 801 | 2025-05-11 | 4 |
| 1204 | 801 | 2025-05-12 | 9 |
| 1204 | 802 | 2025-05-09 | 10 |
| 1204 | 802 | 2025-05-14 | 8 |
| 1204 | 802 | 2025-05-15 | 3 |
| 1204 | 802 | 2025-05-20 | 2 |
| 3318 | 801 | 2025-05-09 | 2 |
Output
| rollback_id | units_before | units_during | lifted |
|---|---|---|---|
| 1 | 12 | 15 | yes |
| 2 | 18 | 5 | no |
| 3 | 0 | 0 | no |
| 4 | 0 | 2 | yes |
How to solve Walmart: Did the Rollback Lift Daily Unit Sales?
Each rollback defines its own windows, so the sales are joined with a range condition: same store and item, and a sale_date between start_date - 7 and end_date. One LEFT JOIN brings both windows; a CASE inside SUM then splits the rows into those before the start and those from the start on. The LEFT JOIN keeps a rollback whose item sold nothing, and COALESCE turns its NULL sums into 0.
The subtle part is the averages. Days without sales have no row, so AVG(units) would average only the days that sold something and overstate both rates. The right denominators are calendar days: 7 for the week before, and DATEDIFF(end_date, start_date) + 1 for the rollback (the +1 because both ends count). Rather than divide, compare units_during * 7 > units_before * days — the same inequality, in integers, with no rounding question. When both totals are 0 the comparison is false, so a rollback that sold nothing at all did not lift sales.
Sales at another store, or of another item, never enter the windows thanks to the join keys. The alternative computes the two totals with correlated scalar subqueries and compares the decimal averages directly. Both cost one range lookup per rollback with an index on (store_id, item_id, sale_date).
Reference solution (MySQL)
WITH t AS (
SELECT r.rollback_id,
DATEDIFF(r.end_date, r.start_date) + 1 AS days,
COALESCE(SUM(CASE WHEN d.sale_date < r.start_date THEN d.units END), 0) AS units_before,
COALESCE(SUM(CASE WHEN d.sale_date >= r.start_date THEN d.units END), 0) AS units_during
FROM ItemRollback r
LEFT JOIN DailyItemSales d
ON d.store_id = r.store_id AND d.item_id = r.item_id
AND d.sale_date BETWEEN DATE_SUB(r.start_date, INTERVAL 7 DAY) AND r.end_date
GROUP BY r.rollback_id, r.start_date, r.end_date
)
SELECT rollback_id, units_before, units_during,
CASE WHEN units_during * 7 > units_before * days THEN 'yes' ELSE 'no' END AS lifted
FROM tAnother way
SELECT rollback_id, units_before, units_during,
IF(units_during / (DATEDIFF(end_date, start_date) + 1) > units_before / 7, 'yes', 'no') AS lifted
FROM (
SELECT r.rollback_id, r.start_date, r.end_date,
(SELECT COALESCE(SUM(d.units), 0) FROM DailyItemSales d
WHERE d.store_id = r.store_id AND d.item_id = r.item_id
AND d.sale_date >= DATE_SUB(r.start_date, INTERVAL 7 DAY) AND d.sale_date < r.start_date) AS units_before,
(SELECT COALESCE(SUM(d.units), 0) FROM DailyItemSales d
WHERE d.store_id = r.store_id AND d.item_id = r.item_id
AND d.sale_date BETWEEN r.start_date AND r.end_date) AS units_during
FROM ItemRollback r
) t← Walmart: Late Store Deliveries by Day of the Week · Walmart: Walmart+ Membership Spells, Bridging Short Lapses →