Slow-Moving SKUs Still Holding Stock

Medium
387.

Slow-Moving SKUs Still Holding Stock

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

ColumnType
skuvarchar
product_namevarchar

Primary key: sku.

One row per product in the range.

Table: StockOnHand

ColumnType
skuvarchar
on_handint

Primary key: sku.

Current chain-wide stock of every product; every product has exactly one row.

Table: Sale

ColumnType
sale_idint
skuvarchar
sold_ondate
qtyint

Primary key: sale_id.

One row per sale line; sku is always in Product.

Example

The rows must come back in the order shown.

Product

skuproduct_name
GRO-1003Atta 10kg
BEV-3002Filter Coffee 500g
PER-4002Coconut Hair Oil 500ml
HOM-5002Floor Cleaner 1L
SNK-6003Khakhra Methi 200g

StockOnHand

skuon_hand
GRO-100342
BEV-300215
PER-40020
HOM-500260
SNK-60038

Sale

sale_idskusold_onqty
1GRO-10032025-04-303
2GRO-10032025-03-112
3BEV-30022025-05-011
4PER-40022025-01-204
5SNK-60032025-02-146

Output

skuproduct_nameon_handlast_sold_on
HOM-5002Floor Cleaner 1L60NULL
SNK-6003Khakhra Methi 200g82025-02-14
GRO-1003Atta 10kg422025-04-30
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.

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

ColumnType
skuvarchar
product_namevarchar

Primary key: sku.

One row per product in the range.

Table: StockOnHand

ColumnType
skuvarchar
on_handint

Primary key: sku.

Current chain-wide stock of every product; every product has exactly one row.

Table: Sale

ColumnType
sale_idint
skuvarchar
sold_ondate
qtyint

Primary key: sale_id.

One row per sale line; sku is always in Product.

Examples

Example 1

Product

skuproduct_name
GRO-1003Atta 10kg
BEV-3002Filter Coffee 500g
PER-4002Coconut Hair Oil 500ml
HOM-5002Floor Cleaner 1L
SNK-6003Khakhra Methi 200g

StockOnHand

skuon_hand
GRO-100342
BEV-300215
PER-40020
HOM-500260
SNK-60038

Sale

sale_idskusold_onqty
1GRO-10032025-04-303
2GRO-10032025-03-112
3BEV-30022025-05-011
4PER-40022025-01-204
5SNK-60032025-02-146

Output

skuproduct_nameon_handlast_sold_on
HOM-5002Floor Cleaner 1L60NULL
SNK-6003Khakhra Methi 200g82025-02-14
GRO-1003Atta 10kg422025-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.sku

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

Another 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 →