# Never-Ordered Products

> In the catalog. Never purchased.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Which products in the catalog have never been ordered? Show product ID and product name for items with no matching transaction.

## Worked solution and explanation

### Why this problem exists in real interviews

Joining products to transactions and then applying anti-join reveals whether you can manage grain changes across tables. Interviewers pick this as a fundamentals check because a wrong join order or missed GROUP BY silently inflates the result.

---

### Break down the requirements

#### Step 1: LEFT JOIN products to transactions

`FROM products LEFT JOIN transactions ON products.product_id = transactions.product_id` keeps all products, even those with no matching transaction.

#### Step 2: Filter for unmatched rows

`WHERE transactions.transaction_id IS NULL` isolates products that never appeared in a transaction.

#### Step 3: Return product identifiers

Select `product_id` and `product_name` from the products table.

---

### The solution

**LEFT JOIN anti-pattern**

```sql
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN transactions t ON p.product_id = t.product_id
WHERE t.transaction_id IS NULL
```

> **Cost Analysis**
>
> With `products` (20,000 rows), `transactions` (50,000,000 rows), the full scan reads significant data. A composite index on the filter columns pushes the predicate into the index layer. Pre-aggregation in a materialized view is worth considering at this scale.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- What happens to your result if products.rating contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on rating.)_
- How would the result differ if you used NOT IN instead of LEFT JOIN ... IS NULL against products? _(Tests knowledge of NOT IN behavior with NULLs versus NOT EXISTS and LEFT JOIN anti-patterns.)_
- With millions of distinct values in transactions.transaction_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like transaction_id.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/never_ordered_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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.