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
| Column | Type |
|---|---|
| rollback_id | int |
| item_name | varchar |
| department | varchar |
| was_price | decimal |
| rollback_price | decimal |
| start_date | date |
| end_date | date |
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_id | item_name | department | was_price | rollback_price | start_date | end_date |
|---|---|---|---|---|---|---|
| 1 | Whole Milk 1 gal | Dairy | 3.98 | 3.48 | 2025-03-01 | 2025-03-31 |
| 2 | 50-inch 4K TV | Electronics | 298 | 248 | 2025-03-10 | 2025-03-15 |
| 3 | Ibuprofen 200 ct | Pharmacy | 8.97 | 8.97 | 2025-03-01 | 2025-03-20 |
| 4 | Bath Towel Set | Home | 6.97 | 4.97 | 2025-03-16 | 2025-04-15 |
| 5 | Camping Cooler 48 qt | Outdoor | 49.97 | 29.97 | 2025-02-01 | 2025-03-14 |
| 6 | Die-Cast Cars 20-pack | Toys | 21.97 | 19.97 | 2025-03-15 | 2025-03-29 |
| 7 | Sandwich Bread | Bakery | 2.48 | 1.98 | 2025-03-05 | 2025-03-25 |
Output
| rollback_id | item_name | savings |
|---|---|---|
| 2 | 50-inch 4K TV | 50 |
| 6 | Die-Cast Cars 20-pack | 2 |
| 1 | Whole Milk 1 gal | 0.5 |
| 7 | Sandwich Bread | 0.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_idAnother 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 →