Walmart: Clearance Items Marked Down Three Times With No Sale
When clearance stock does not move, a Walmart merchandising analyst flags it for a final markdown or a return to vendor.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #1008
Problem statement
When clearance stock does not move, a Walmart merchandising analyst flags it for a final markdown or a return to vendor. The flag is: three or more markdowns and no sale on or after the first markdown.
For each item meeting both conditions, return item_id, markdowns (how many markdowns it had) and last_price (the new_price of its most recent markdown). Sales before the first markdown do not matter. Return the rows 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: Markdown
| Column | Type |
|---|---|
| markdown_id | int |
| item_id | int |
| markdown_date | date |
| new_price | decimal |
Primary key: markdown_id.
One row per price cut on a clearance item. An item never has two markdowns on the same date.
Table: ClearanceSale
| Column | Type |
|---|---|
| sale_id | int |
| item_id | int |
| sale_date | date |
| units | int |
Primary key: sale_id.
One row per register line for the item.
Examples
Example 1
Markdown
| markdown_id | item_id | markdown_date | new_price |
|---|---|---|---|
| 1 | 701 | 2025-01-05 | 19 |
| 2 | 701 | 2025-01-19 | 14 |
| 3 | 701 | 2025-02-02 | 9 |
| 4 | 702 | 2025-01-05 | 40 |
| 5 | 702 | 2025-01-20 | 30 |
| 6 | 702 | 2025-02-04 | 22 |
| 7 | 702 | 2025-02-18 | 15 |
| 8 | 703 | 2025-01-10 | 12 |
| 9 | 703 | 2025-01-24 | 8 |
| 10 | 703 | 2025-02-07 | 5 |
| 11 | 704 | 2025-01-12 | 6 |
| 12 | 704 | 2025-01-26 | 4 |
ClearanceSale
| sale_id | item_id | sale_date | units |
|---|---|---|---|
| 100 | 701 | 2025-01-02 | 3 |
| 101 | 703 | 2025-01-10 | 1 |
| 102 | 704 | 2025-03-01 | 2 |
Output
| item_id | markdowns | last_price |
|---|---|---|
| 701 | 3 | 9 |
| 702 | 4 | 15 |
How to solve Walmart: Clearance Items Marked Down Three Times With No Sale
Three facts about each item are needed: its markdown count, the date of its first markdown (to test sales against) and the price of its last markdown. The count and both dates come from one GROUP BY item_id, with HAVING COUNT(*) >= 3 keeping only items cut at least three times.
The last price cannot be taken with an aggregate — MIN(new_price) would usually match, but nothing guarantees a later markdown is cheaper — so the query joins back to Markdown on the item and its latest date. Because an item has at most one markdown per date, that join finds exactly one row.
The sales condition is an anti join: keep the item when no sale exists dated on or after first_md. A sale before clearance began does not save the item, and a sale on the very day of the first markdown does, since "on or after" includes it.
The alternative does it in one pass with window functions — ROW_NUMBER descending marks the latest markdown, COUNT and MIN over the item supply the rest — and expresses the anti join as a LEFT JOIN that found nothing.
Reference solution (MySQL)
WITH md AS (
SELECT item_id, COUNT(*) AS markdowns, MIN(markdown_date) AS first_md, MAX(markdown_date) AS last_md
FROM Markdown
GROUP BY item_id
HAVING COUNT(*) >= 3
)
SELECT md.item_id, md.markdowns, m.new_price AS last_price
FROM md
JOIN Markdown m ON m.item_id = md.item_id AND m.markdown_date = md.last_md
WHERE NOT EXISTS (
SELECT 1 FROM ClearanceSale s
WHERE s.item_id = md.item_id AND s.sale_date >= md.first_md
)Another way
WITH r AS (
SELECT item_id, new_price,
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY markdown_date DESC) AS rn,
COUNT(*) OVER (PARTITION BY item_id) AS markdowns,
MIN(markdown_date) OVER (PARTITION BY item_id) AS first_md
FROM Markdown
)
SELECT r.item_id, r.markdowns, r.new_price AS last_price
FROM r
LEFT JOIN ClearanceSale s ON s.item_id = r.item_id AND s.sale_date >= r.first_md
WHERE r.rn = 1 AND r.markdowns >= 3 AND s.sale_id IS NULL← Walmart: Shelf Gaps Still Open at the Afternoon Walk · Walmart: Substitutions Made Against the Customer's Wishes →