Slow-Moving SKUs Still Holding Stock — SQL Problem
Before the end-of-season markdown, merchandisers want the products that are tying up shelf space: SKUs with stock on hand (on_hand greater than 0) that have…
- Difficulty: Medium
- Topics: Subqueries, Dates, Joins
- Dialect: MySQL
- Problem: #387
Problem statement
Before the end-of-season markdown, merchandisers want the products that are tying up shelf space: SKUs with stock on hand (on_hand greater than 0) that have not sold since 30 April 2025, meaning no sale on or after 2025-05-01. A SKU that has never sold at all is slow-moving too.
Return sku, product_name, on_hand and last_sold_on (the date of its most recent sale ever, NULL if it never sold). Order the rows with never-sold SKUs first, then by last_sold_on oldest first, then by sku.
Tables
Table: Product
| Column | Type |
|---|---|
| sku | varchar |
| product_name | varchar |
Primary key: sku.
One row per product in the range.
Table: StockOnHand
| Column | Type |
|---|---|
| sku | varchar |
| on_hand | int |
Primary key: sku.
Current chain-wide stock of every product; every product has exactly one row.
Table: Sale
| Column | Type |
|---|---|
| sale_id | int |
| sku | varchar |
| sold_on | date |
| qty | int |
Primary key: sale_id.
One row per sale line; sku is always in Product.
Examples
Example 1
Product
| sku | product_name |
|---|---|
| GRO-1003 | Atta 10kg |
| BEV-3002 | Filter Coffee 500g |
| PER-4002 | Coconut Hair Oil 500ml |
| HOM-5002 | Floor Cleaner 1L |
| SNK-6003 | Khakhra Methi 200g |
StockOnHand
| sku | on_hand |
|---|---|
| GRO-1003 | 42 |
| BEV-3002 | 15 |
| PER-4002 | 0 |
| HOM-5002 | 60 |
| SNK-6003 | 8 |
Sale
| sale_id | sku | sold_on | qty |
|---|---|---|---|
| 1 | GRO-1003 | 2025-04-30 | 3 |
| 2 | GRO-1003 | 2025-03-11 | 2 |
| 3 | BEV-3002 | 2025-05-01 | 1 |
| 4 | PER-4002 | 2025-01-20 | 4 |
| 5 | SNK-6003 | 2025-02-14 | 6 |
Output
| sku | product_name | on_hand | last_sold_on |
|---|---|---|---|
| HOM-5002 | Floor Cleaner 1L | 60 | NULL |
| SNK-6003 | Khakhra Methi 200g | 8 | 2025-02-14 |
| GRO-1003 | Atta 10kg | 42 | 2025-04-30 |
How to solve Slow-Moving SKUs Still Holding Stock
A SKU is slow-moving when two facts hold: it has stock, and no sale falls on or after the cut-off. The second fact is an absence, so NOT EXISTS (… s.sold_on >= '2025-05-01') states it directly; it is also automatically true for a SKU that never sold. The last sale date is a separate, informational column — a correlated MAX(sold_on) that is NULL for never-sold SKUs.
The equivalent formulation computes each SKU's latest sale once (a grouped derived table) and keeps SKUs whose latest sale is NULL or before the cut-off. Forgetting the IS NULL branch is the classic bug: NULL < '2025-05-01' is unknown, so never-sold products — the slowest movers of all — would vanish. The third version groups a LEFT JOIN and tests the same condition in HAVING.
Zero-stock SKUs are dropped because there is nothing to mark down. For the order, MySQL and most engines sort NULL first in ascending order, but spelling it out with a CASE (or IS NOT NULL as a sort key) makes the intent explicit. Each approach scans the sales once per SKU at most; an index on Sale(sku, sold_on) makes the probes cheap.
Reference solution (MySQL)
SELECT p.sku, p.product_name, h.on_hand,
(SELECT MAX(s.sold_on) FROM Sale s WHERE s.sku = p.sku) AS last_sold_on
FROM Product p
JOIN StockOnHand h ON h.sku = p.sku
WHERE h.on_hand > 0
AND NOT EXISTS (SELECT 1 FROM Sale s WHERE s.sku = p.sku AND s.sold_on >= '2025-05-01')
ORDER BY CASE WHEN last_sold_on IS NULL THEN 0 ELSE 1 END, last_sold_on, p.skuAnother way
SELECT p.sku, p.product_name, h.on_hand, l.last_sold_on
FROM Product p
JOIN StockOnHand h ON h.sku = p.sku
LEFT JOIN (SELECT sku, MAX(sold_on) AS last_sold_on FROM Sale GROUP BY sku) l ON l.sku = p.sku
WHERE h.on_hand > 0 AND (l.last_sold_on IS NULL OR l.last_sold_on < '2025-05-01')
ORDER BY l.last_sold_on IS NOT NULL, l.last_sold_on, p.skuAnother way
SELECT p.sku, p.product_name, h.on_hand, MAX(s.sold_on) AS last_sold_on
FROM Product p JOIN StockOnHand h ON h.sku = p.sku LEFT JOIN Sale s ON s.sku = p.sku
WHERE h.on_hand > 0
GROUP BY p.sku, p.product_name, h.on_hand
HAVING MAX(s.sold_on) IS NULL OR MAX(s.sold_on) < '2025-05-01'
ORDER BY COALESCE(MAX(s.sold_on), '0000-01-01'), p.sku← Store Revenue Against the Previous Calendar Month · Duplicate Vendors After Cleaning Up Their Names →