# Ghost Products

> Listed but never sold. The shelves collect dust.

Canonical URL: <https://datadriven.io/problems/ghost_products>

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.

## Tables

### products

- product_id INTEGER
- product_name TEXT
- category TEXT
- price REAL
- rating REAL
- in_stock INTEGER

Sample rows (10 of 200):

| product_id | product_name | category | price | rating | in_stock |
| --- | --- | --- | --- | --- | --- |
| 1001 | Basic Device 1X | Books | 17.52 | 2.7 | 1 |
| 1050 | Deluxe Bundle 2X | Clothing | 25.05 | 4.4 | 1 |
| 1099 | Pro Unit 3X | Home & Kitchen | 32.58 | 2.1 | 1 |
| 1148 | Ultra Tool 4X | Collectibles | 40.11 | 3.8 | 1 |
| 1197 | Essential Set 5X | Toys | 47.64 | 1.5 | 1 |
| 1246 | Classic System 6X | Beauty | 0 | 3.2 | 1 |
| 1295 | Smart Gadget 7X | Automotive | 62.7 | 4.9 | 0 |
| 1344 | Eco Kit 8X | Garden | 70.23 | 2.6 | 1 |
| 1393 | Turbo Pack 9X | Music | 77.76 | 4.3 | 0 |
| 1442 | Premium Widget 10X | Electronics | 85.29 | 2 | 1 |

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 0 | 0 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-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**

```sql
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
```

> **Cost Analysis**
>
> Standard anti-join. An index on `transactions(product_id)` makes the join efficient.

> **Interviewers Watch For**
>
> The interviewer watches for the anti-join pattern choice. NOT IN with NULLs in the subquery is a classic trap.

> **Common Pitfall**
>
> `NOT IN (SELECT product_id FROM transactions)` fails if any `product_id` in transactions is NULL. Use LEFT JOIN + IS NULL or NOT EXISTS.

---

## Common follow-up questions

- Why does NOT IN fail with NULLs in the subquery? _(Tests three-valued logic understanding.)_
- How would you find products added in the last 30 days with no sales? _(Tests adding a date filter.)_
- Which anti-join pattern is fastest? _(Tests LEFT JOIN vs. NOT EXISTS performance.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/ghost_products)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.