Walmart: Clearance Items Marked Down Three Times With No Sale

Medium
1008.

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. 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

ColumnType
markdown_idint
item_idint
markdown_datedate
new_pricedecimal

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

ColumnType
sale_idint
item_idint
sale_datedate
unitsint

Primary key: sale_id.

One row per register line for the item.

Example

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

Markdown

markdown_iditem_idmarkdown_datenew_price
17012025-01-0519
27012025-01-1914
37012025-02-029
47022025-01-0540
57022025-01-2030
67022025-02-0422
77022025-02-1815
87032025-01-1012
97032025-01-248
107032025-02-075
2 more rows

ClearanceSale

sale_iditem_idsale_dateunits
1007012025-01-023
1017032025-01-101
1027042025-03-012

Output

item_idmarkdownslast_price
70139
702415
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: 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

ColumnType
markdown_idint
item_idint
markdown_datedate
new_pricedecimal

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

ColumnType
sale_idint
item_idint
sale_datedate
unitsint

Primary key: sale_id.

One row per register line for the item.

Examples

Example 1

Markdown

markdown_iditem_idmarkdown_datenew_price
17012025-01-0519
27012025-01-1914
37012025-02-029
47022025-01-0540
57022025-01-2030
67022025-02-0422
77022025-02-1815
87032025-01-1012
97032025-01-248
107032025-02-075
117042025-01-126
127042025-01-264

ClearanceSale

sale_iditem_idsale_dateunits
1007012025-01-023
1017032025-01-101
1027042025-03-012

Output

item_idmarkdownslast_price
70139
702415

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 →