Level 2 · Find the slow movers in SQL

What you are checking, and why

Before ranking anything, decide which products can fairly be judged. Three groups behave differently:

• Retired products are already leaving the range; they belong to a different review.
• New in 2025 products had only part of the year on sale. A lantern launched in August cannot be compared with one that sold all summer.
• Established products were on sale for all twelve months: these are the ones to rank.

Step 1 labels every product with a CASE and counts. It starts from products with a LEFT JOIN to the 2025 sales, so a product that sold nothing would still appear, with NULL sales:

FROM products AS p
LEFT JOIN sales AS s ON s.product_id = p.product_id

COUNT(s.product_id) then counts only the products that sold. The same step counts missing unit costs, because a slow product with an unknown cost is one whose margin nobody can judge.

Ranking inside each category

Step 2 needs two numbers for every product that depend on its category: the category’s total and the product’s rank. Window functions give both without collapsing the rows:

s.revenue * 100.0 / SUM(s.revenue) OVER (PARTITION BY p.category) AS share,
ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY s.revenue) AS rn

PARTITION BY restarts the calculation for each category. Because window functions run after WHERE, the filter rn <= 3 goes in the outer query.

Step 3 ranks again and then aggregates: SUM(CASE WHEN rn <= 3 THEN revenue ELSE 0 END) keeps the weakest three, SUM(revenue) the whole category.

Reading the result

category	weakest product	units_2025	category_share_pct
Furniture	Upland Oak Bookcase	9	0.7
Textiles	Spruce Wool Duvet Cover	25	0.7
Outdoor	Orchard Aluminum Adirondack Chair	93	1.0
Lighting	Fern Rattan Desk Lamp	13	1.2

Revenue share and units together tell the story. The bookcase and the desk lamp sold almost nothing; the Adirondack chair sold 93 units but at $51.99 each, so it barely registers in a category of patio sets and grills.

Pitfalls

• A product with NULL sales falls out of an inner join; start from products when you need every product.
• ROW_NUMBER breaks ties arbitrarily; with revenue to the cent ties are rare, but say which rule you used.
• A share of revenue says nothing about profit. Seven established products have no unit cost, so their margin is unknown.