Unmatched Categories

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

DoubleVerify

Problem

Find the product categories with three or more items currently out of stock, for the restock list a merchandising analyst pulls from the products table.

Categories with nothing on the shelf. Empty aisles.

Tables

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

Worked solution and explanation

This is a threshold on a whole group dressed up as a restock report. Anyone can find sold-out products. What separates candidates is noticing that 'three or more out of stock' describes a category, not a row: you have to count the empty slots per category and only then decide. The reflex SELECT DISTINCT category FROM products WHERE in_stock = 0 answers 'has any sold-out product'. Every category on this catalog does, so it hands back all 11 and the restock list is worthless.

Count, then decide

Step 1: Turn the flag into a sold-out count

in_stock is 1 for buyable and 0 for sold out, so 1 - in_stock is 1 exactly on the empty slots. SUM(1 - in_stock) per category is the number of sold-out products, with no CASE needed.

Step 2: Test the count after grouping

WHERE runs before GROUP BY and only sees one product at a time, so it cannot know a category's total. The condition belongs in HAVING SUM(1 - in_stock) >= 3, which is evaluated once per category.

Step 3: Respect the boundary

Books, Automotive and Garden each have exactly three sold-out products. Write > 3 and all three disappear, leaving only Music with its twenty. 'Three or more' is inclusive.

SELECT category
FROM products
GROUP BY category
HAVING SUM(1 - in_stock) >= 3

Row filter

WHERE in_stock = 0 plus DISTINCT category asks whether a category has any sold-out product. All 11 do, including Collectibles with a single empty slot, so the list comes back complete and useless.

Group threshold

GROUP BY category HAVING SUM(1 - in_stock) >= 3 counts first and decides second. Books, Automotive and Garden reach three, Music reaches twenty, and the other seven stop at one or two.

Common follow-up questions

• What if `in_stock` could be `NULL` for some products? (`1 - NULL` is `NULL` and `SUM` skips it, so unknown stock silently counts as available; decide whether it should.)
• How would you also return each flagged category's sold-out count? (Add `SUM(1 - in_stock)` to the select list under a clear name; the grouping already supports it.)
• What if the bar became a share, such as under 85% in stock? (Tests whether you compare `AVG(in_stock)` instead of integer-dividing two counts.)