Double or Nothing

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

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.

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

Tables

products

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

Expected output

Worked solution and explanation

What this really is

This is unordered pair enumeration wearing a pricing costume. Joining products to itself on category is the part everyone gets. What separates candidates is what comes after p1.product_id < p2.product_id: that guard orders each pair by id, and id says nothing about price, so the cheaper product can land on either side. Write only p1.price >= 2 * p2.price and you silently drop roughly half of the 978 qualifying pairs, every one where the lower id happens to be the cheaper product. Swap the < for != and you fail the other way, reporting every pair twice.

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

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

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.)