Unsold Product Categories

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

General Assembly

Problem

Dead inventory sits in the products catalog without ever appearing in transactions, and the merchandising team's audit sizes it one category at a time. Report what percentage of each category's products are dead.

Dead inventory inflating storage costs.

Tables

products

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

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

Beneath the dead-inventory story this is a per-category anti-join: for each category, find the products with NO matching transaction, then express that count as a share of the whole category. Anyone can spot the LEFT JOIN. The catch lives in the denominator. A product that sold sits on thousands of transaction rows, so joining products straight to the 60M-row transactions table fans every sold product out into a pile of duplicate rows. Count product_id plainly and you are counting rows, not products: the category total balloons and the unsold share collapses toward zero. COUNT(DISTINCT p.product_id) is what pulls the true product count back out. Miss it and every dead-inventory category reads as perfectly healthy.

Break down the requirements

Step 1: Keep every product with a left join

LEFT JOIN products to transactions on product_id so every product survives, including the ones that never sold. An inner join would silently discard exactly the products you are trying to find.

Step 2: Count the unsold products

A product that never sold has no match, so every transaction column on the joined side comes back NULL. SUM(CASE WHEN t.transaction_id IS NULL THEN 1 ELSE 0 END) counts those unsold products, and because an unmatched product produces exactly one NULL row, the fan-out from sold products never touches this count.

Step 3: Divide by the true product count

Divide by COUNT(DISTINCT p.product_id), not a plain COUNT. The direct join multiplies each sold product by its transaction count, so only DISTINCT recovers the real number of products in the category. Multiply the numerator by 100.0 to force float division and avoid integer-truncated zeros.

The solution

SELECT
    p.category,
    SUM(CASE WHEN t.transaction_id IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT p.product_id) AS unsold_pct
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
GROUP BY p.category

Common follow-up questions

• How would you also show the total number of products per category? (Add COUNT(DISTINCT `p.product_id`) as a separate column in the same query.)
• What if you needed to consider only transactions from the last year? (Add a date predicate on `t.transaction_date` inside the join or a filtered aggregate so unmatched products still survive.)
• How would you find the category with the highest unsold percentage? (Wrap in an outer query with ORDER BY `unsold_pct` DESC LIMIT 1.)