# Double or Nothing

> Same shelf, wildly different stickers. Spot the pricing gaps.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

A pricing analyst is flagging products on the same shelf that carry wildly different stickers. Within each category, find every pair where one product costs at least twice the other, listing each pair only once.

## Worked solution and explanation

### What this really is

This is a deduplication problem wearing a pricing costume. Joining `products` to itself on `category` is the part everyone gets. The whole challenge lives in the `p1.product_id < p2.product_id` guard: without it, every qualifying pair surfaces twice, once as (A, B) and once as (B, A), and your row count doubles. Get that wrong and the analyst's report claims twice as many price gaps as actually exist.

> **Trick to Solving**
>
> "List each pair once" is the tell that you need a strict ordering on the self-join, not just an inequality.
> 
> 1. Self-join `products` on `category`
> 2. Enforce `p1.product_id < p2.product_id` to keep one direction per pair
> 3. Apply the ratio filter: `p1.price >= 2 * p2.price OR p2.price >= 2 * p1.price`

---

### Break down the requirements

#### Step 1: Self-join products within a category

`JOIN products p2 ON p1.category = p2.category AND p1.product_id < p2.product_id` pairs each product with the higher-id products in its own category, and only those. The `<` does double duty: it blocks a product pairing with itself and it keeps exactly one ordering of each pair.

#### Step 2: Apply the 2x price condition

`WHERE p1.price >= 2 * p2.price OR p2.price >= 2 * p1.price` keeps a pair when either side is at least double the other. You need both directions because the ordering key is product_id, not price, so the cheaper product can sit on either side of the join.

#### Step 3: Return pair details

Select both product names, the shared category, and both prices. The category is shared, so `p1.category` is enough.

---

### The solution

**Self-join products within a category to find price gaps**

```sql
SELECT
    p1.product_name AS product_1,
    p2.product_name AS product_2,
    p1.category,
    p1.price AS price_1,
    p2.price AS price_2
FROM products p1
JOIN products p2
  ON p1.category = p2.category
  AND p1.product_id < p2.product_id
WHERE p1.price >= 2 * p2.price
   OR p2.price >= 2 * p1.price
```

> **Cost Analysis**
>
> With 20,000 rows the self-join is a bounded intra-category product: because pairs form only within a category, the join fans out per category rather than across the whole table, so it stays cheap at this size. At production scale, an index on `category` lets the engine build each category's pair set without a full cross scan, and the `product_id < product_id` guard keeps the output at half the naive pair count.

> **Interviewers Watch For**
>
> The signal of seniority is reaching for the ordering key immediately. A candidate who writes the join with `!=` and only later notices duplicate rows is debugging; the one who reaches for `<` up front has internalized why unordered pairs need a tie-break.

> **Common Pitfall**
>
> The classic miss is `p1.product_id != p2.product_id` instead of `<`. Inequality still keeps both orderings of every pair, so you dedupe nothing and every pair appears twice. Only a strict one-directional comparison collapses (A, B) and (B, A) into a single row. A second trap: writing `p1.price > 2 * p2.price` and silently dropping the exactly-double pairs the `>=` is meant to keep.

---

## Common follow-up questions

- If `category` is NULL for some products, which pairs does your join quietly drop, and is that the behavior you want? _(Tests null handling in the join key: NULL category never equals NULL, so those rows silently drop from an inner join.)_
- How would you change the query so it only compares products that are both currently in stock? _(Tests adding a filter predicate without breaking the dedup or ratio logic.)_
- Suppose you also wanted the price ratio in the output, widest gap first. How would you compute and sort by it? _(Tests computing a derived column and ordering by it, extending the pair logic.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/double_or_nothing)
- [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.