Above Average Product Prices

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

L4

Asked in

Publicis Sapient

Problem

The finance team defines a product's base price as the lowest transaction amount ever recorded for it. They want to flag products whose base price runs above the average base price across all products. Return the product ID and its base price.

Some products cost more than they should.

Tables

transactions

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

Expected output

Worked solution and explanation

Why this problem exists in real interviews

A derived-threshold filter: compute a per-group aggregate (MIN total_amount per product_id), then keep groups whose aggregate beats the average of that same aggregate across all products. The interviewer is watching whether you name 'average of what' before you write.

Break down the requirements

Step 1: Define base price

Per product_id, base_price = MIN(total_amount) over the transactions table. One row per product after the GROUP BY.

Step 2: Define the threshold

Average of base prices across all products, not the average of every transaction. The scalar subquery is MIN per product first, then AVG over those mins.

Step 3: Filter with HAVING, not WHERE

MIN(total_amount) is an aggregate; the comparison to the scalar subquery belongs in HAVING. WHERE evaluates before aggregation and can't see MIN.

Step 4: Return product_id and base_price

SELECT product_id, MIN(total_amount) AS base_price. No ORDER BY required by the prompt; add one if asked.

The solution

SELECT
  product_id,
  MIN(total_amount) AS base_price
FROM transactions
GROUP BY product_id
HAVING MIN(total_amount) > (
  SELECT AVG(min_price)
  FROM (
    SELECT MIN(total_amount) AS min_price
    FROM transactions
    GROUP BY product_id
  )
)

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Rewrite this using a CTE instead of a nested subquery. (Tests CTE fluency. WITH `product_mins` AS (...) then SELECT FROM `product_mins` WHERE `min_price` > (SELECT AVG(`min_price`) FROM `product_mins`). Same cost, more readable.)
• How does the answer change if refunds are stored as negative `total_amount`? (Probes whether you noticed MIN will pick up the refund. Filter WHERE `total_amount` > 0 inside both the outer and inner aggregation, or define `base_price` on positive rows only.)
• What if base price should be the lowest unit price, `total_amount` / quantity? (Forces a per-row expression inside MIN. Watch for quantity = 0 division and NULL propagation; wrap with NULLIF(quantity, 0).)
• How would you scale this if transactions had a billion rows per month? (Tests partition awareness. Push `transaction_date` predicates into both aggregations, and consider materializing `product_mins` as a daily table so the average is read from a small precomputed set.)