Walmart: Did the Rollback Lift Daily Unit Sales?

Hard
1016.

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.

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

ColumnType
rollback_idint
store_idint
item_idint
start_datedate
end_datedate

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

ColumnType
store_idint
item_idint
sale_datedate
unitsint

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

Units of the item sold at the store that day. Days with no sales have no row.

Example

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

ItemRollback

rollback_idstore_iditem_idstart_dateend_date
112048012025-05-082025-05-11
212048022025-05-152025-05-21
312048032025-05-202025-05-20
433188012025-05-082025-05-11

DailyItemSales

store_iditem_idsale_dateunits
12048012025-04-3020
12048012025-05-015
12048012025-05-034
12048012025-05-073
12048012025-05-086
12048012025-05-105
12048012025-05-114
12048012025-05-129
12048022025-05-0910
12048022025-05-148
3 more rows

Output

rollback_idunits_beforeunits_duringlifted
11215yes
2185no
300no
402yes
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: 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

ColumnType
rollback_idint
store_idint
item_idint
start_datedate
end_datedate

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

ColumnType
store_idint
item_idint
sale_datedate
unitsint

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_idstore_iditem_idstart_dateend_date
112048012025-05-082025-05-11
212048022025-05-152025-05-21
312048032025-05-202025-05-20
433188012025-05-082025-05-11

DailyItemSales

store_iditem_idsale_dateunits
12048012025-04-3020
12048012025-05-015
12048012025-05-034
12048012025-05-073
12048012025-05-086
12048012025-05-105
12048012025-05-114
12048012025-05-129
12048022025-05-0910
12048022025-05-148
12048022025-05-153
12048022025-05-202
33188012025-05-092

Output

rollback_idunits_beforeunits_duringlifted
11215yes
2185no
300no
402yes

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 t

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