Walmart: Active Rollbacks and the Savings on Each

Easy
998.

Walmart: Active Rollbacks and the Savings on Each

EasyBasics

MySQL

A store's pricing team at Walmart walks the aisles on 2025-03-15 to check that every rollback sign on the shelf matches the price file, and wants the list of rollbacks they should find.

A rollback is active on that date when start_date <= '2025-03-15' <= end_date (both ends inclusive). The price file also holds a few bad rows where the rollback price is not actually lower than the was-price; leave those out. Return rollback_id, item_name and savings (the was-price minus the rollback price, rounded to 2 decimals), ordered by savings descending, then rollback_id ascending.

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: RollbackPrice

ColumnType
rollback_idint
item_namevarchar
departmentvarchar
was_pricedecimal
rollback_pricedecimal
start_datedate
end_datedate

Primary key: rollback_id.

One row per rollback in the price file. Prices are in dollars; end_date is the last day the rollback price applies.

Example

The rows must come back in the order shown.

RollbackPrice

rollback_iditem_namedepartmentwas_pricerollback_pricestart_dateend_date
1Whole Milk 1 galDairy3.983.482025-03-012025-03-31
250-inch 4K TVElectronics2982482025-03-102025-03-15
3Ibuprofen 200 ctPharmacy8.978.972025-03-012025-03-20
4Bath Towel SetHome6.974.972025-03-162025-04-15
5Camping Cooler 48 qtOutdoor49.9729.972025-02-012025-03-14
6Die-Cast Cars 20-packToys21.9719.972025-03-152025-03-29
7Sandwich BreadBakery2.481.982025-03-052025-03-25

Output

rollback_iditem_namesavings
250-inch 4K TV50
6Die-Cast Cars 20-pack2
1Whole Milk 1 gal0.5
7Sandwich Bread0.5
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: Active Rollbacks and the Savings on Each

A store's pricing team at Walmart walks the aisles on 2025-03-15 to check that every rollback sign on the shelf matches the price file, and wants the list…

  • Difficulty: Easy
  • Topics: Basics
  • Dialect: MySQL
  • Problem: #998

Problem statement

A store's pricing team at Walmart walks the aisles on 2025-03-15 to check that every rollback sign on the shelf matches the price file, and wants the list of rollbacks they should find.

A rollback is active on that date when start_date <= '2025-03-15' <= end_date (both ends inclusive). The price file also holds a few bad rows where the rollback price is not actually lower than the was-price; leave those out. Return rollback_id, item_name and savings (the was-price minus the rollback price, rounded to 2 decimals), ordered by savings descending, then rollback_id ascending.

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: RollbackPrice

ColumnType
rollback_idint
item_namevarchar
departmentvarchar
was_pricedecimal
rollback_pricedecimal
start_datedate
end_datedate

Primary key: rollback_id.

One row per rollback in the price file. Prices are in dollars; end_date is the last day the rollback price applies.

Examples

Example 1

RollbackPrice

rollback_iditem_namedepartmentwas_pricerollback_pricestart_dateend_date
1Whole Milk 1 galDairy3.983.482025-03-012025-03-31
250-inch 4K TVElectronics2982482025-03-102025-03-15
3Ibuprofen 200 ctPharmacy8.978.972025-03-012025-03-20
4Bath Towel SetHome6.974.972025-03-162025-04-15
5Camping Cooler 48 qtOutdoor49.9729.972025-02-012025-03-14
6Die-Cast Cars 20-packToys21.9719.972025-03-152025-03-29
7Sandwich BreadBakery2.481.982025-03-052025-03-25

Output

rollback_iditem_namesavings
250-inch 4K TV50
6Die-Cast Cars 20-pack2
1Whole Milk 1 gal0.5
7Sandwich Bread0.5

How to solve Walmart: Active Rollbacks and the Savings on Each

Everything the pricing team needs is on one row, so this is a filter, a computed column and a sort.

A rollback is live on the audit day when the day lies between its start and end dates, both included — '2025-03-15' BETWEEN start_date AND end_date says exactly that, and it keeps a rollback that starts or ends on the 15th. The bad rows are the ones where rollback_price is equal to or above was_price; the strict comparison rollback_price < was_price drops both kinds.

The savings is the difference of the two prices. Prices with cents are not always exact in binary floating point, so rounding the difference to two decimals both tidies the output and makes two equal savings compare equal in the sort. The order is fully specified — savings descending, then rollback_id — so the answer is the same however the rows are stored.

The alternative writes the date test as two comparisons and the price test as a positive difference. Either way it is one scan of the table.

Reference solution (MySQL)

SELECT rollback_id, item_name, ROUND(was_price - rollback_price, 2) AS savings
FROM RollbackPrice
WHERE rollback_price < was_price
  AND '2025-03-15' BETWEEN start_date AND end_date
ORDER BY savings DESC, rollback_id

Another way

SELECT rollback_id, item_name, ROUND(was_price - rollback_price, 2) AS savings
FROM RollbackPrice
WHERE was_price - rollback_price > 0 AND start_date <= '2025-03-15' AND end_date >= '2025-03-15'
ORDER BY ROUND(was_price - rollback_price, 2) DESC, rollback_id ASC

← Uber: Which Promo Gets Credit for a Rider's First Trip · Walmart: Grocery Pickup Slots Nobody Booked →