Recent Price Drops

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

Every product in products carries an in_stock flag, and every sale in transactions records the product_id sold and its transaction_date. List the ID and name of each product that is in stock or sold within the last seven days, one row per product.

The price just dropped. Who noticed?

Tables

products

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

transactions

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

Expected output

Worked solution and explanation

This is a set union dressed up as a freshness check. A product qualifies for one of two unrelated reasons: a flag on its own row in products, or a sale in transactions within the last seven days. Everyone spots both conditions. What separates candidates is noticing that only one of them needs transactions at all. Join first and filter on 'sold recently OR in stock' afterwards, and the inner join has already thrown away every product that never sold, which is over a hundred of the 200, in stock or not. The products that survive come back once per sale.

The trap: one join, one OR

SELECT p.product_id, p.product_name
FROM products p
JOIN transactions t ON p.product_id = t.product_id
WHERE t.transaction_date >= DATE('now', '-7 day')
   OR p.in_stock = 1

Build it as two lists

Step 1: Collect the products sold in the past week

Join products to transactions on product_id and keep sales with transaction_date >= DATE('now', '-7 day'). This side genuinely needs the join: the sale date lives only in transactions. Rows with a NULL product_id match nothing and drop out, which is right.

Step 2: Collect the in-stock products straight from products

in_stock = 1 is a fact about the product row, so read it from products alone. No join is the whole point: a product with zero sales is still a candidate here.

Step 3: Merge the two lists with UNION

A product can sit in both lists, in stock and sold this week. UNION (not UNION ALL) collapses that overlap so every product appears once. Both branches select product_id, product_name in the same order, which is what lets them stack.

The solution

SELECT DISTINCT p.product_id, p.product_name
FROM products p
JOIN transactions t ON p.product_id = t.product_id
WHERE t.transaction_date >= DATE('now', '-7 day')
UNION
SELECT product_id, product_name
FROM products
WHERE in_stock = 1

Join, then OR

Every row must survive the inner join to transactions, so products with no sales are gone before the OR runs, and repeat sales repeat the product.

Two branches, then UNION

The stock branch never touches transactions, the sales branch is narrowed to one row per product, and UNION removes products that qualify both ways.

Common follow-up questions

• How would you also return why each product qualified: in stock, sold this week, or both? (Tests moving from a set union to per-product flags, for example a `LEFT JOIN` from `products` to a pre-collapsed recent-sales set.)
• Some `transactions` rows have a `NULL` `product_id`. What happens to them in each branch? (Tests that the inner join drops them, which is correct: a sale with no product cannot make a product qualify.)
• If 'the last seven days' meant the store's local time rather than UTC, what changes? (Tests date versus timestamp reasoning around the `DATE('now', '-7 day')` cutoff.)