Ghost Products

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
L5

Problem

Merchandising is hunting for dead inventory that has never sold. Return the product_name of every product in products with no matching row in transactions, walking the list alphabetically from A to Z so the team can check each one off in order.

Listed but never sold. The shelves collect dust.

Tables

products

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

products: 10 of 200 rows
product_idproduct_namecategorypriceratingin_stock
1001Basic Device 1XBooks17.522.71
1050Deluxe Bundle 2XClothing25.054.41
1099Pro Unit 3XHome & Kitchen32.582.11
1148Ultra Tool 4XCollectibles40.113.81
1197Essential Set 5XToys47.641.51
1246Classic System 6XBeauty03.21
1295Smart Gadget 7XAutomotive62.74.90
1344Eco Kit 8XGarden70.232.61
1393Turbo Pack 9XMusic77.764.30
1442Premium Widget 10XElectronics85.2921

transactions

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

transactions: 10 of 200 rows
transaction_iduser_idproduct_idquantitytotal_amounttransaction_date
10671971001223.462026-02-02
11342941050336.932026-03-03
12013911099450.42026-04-04
12684881148563.872026-05-05
13355851197002026-06-06
14026821246290.812026-07-07
146977912953104.282026-08-08
153687613444117.752026-09-09
160397313935-452026-01-10
1670107014421144.692026-02-11

Expected output

First 5 of 109 rows
product_name
Basic Device 11X
Basic Set v10
Basic Set v100
Basic Set v20
Basic Set v30

Worked solution and explanation

Why this problem exists in real interviews

Finding products with no sales tests the anti-join pattern. The interviewer checks whether you use LEFT JOIN + IS NULL, NOT EXISTS, or NOT IN, and understand the NULL caveat with NOT IN.

Break down the requirements

Step 1: Left join products to transactions

LEFT JOIN products to transactions on product_id so every product is kept even with no sale

Step 2: Filter for unmatched products

Keep only rows where the transactions side is NULL (the anti-join: product never sold)

The solution

Anti-join for unsold products
SELECT p.product_name
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
WHERE t.product_id IS NULL
ORDER BY p.product_name

Common follow-up questions