The Premium Shelf

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

L3

Asked in

IBM

Problem

A product's average unit price is the dollars it sold divided by the units it sold, so every unit counts once no matter how large the order it came in. For the merchandising team's premium shelf, list every product whose average unit price across the transactions table reaches $100, with its name from the products table, lowest product ID first.

The catalog's high end, weighed one unit at a time.

Tables

transactions

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

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 problem is really testing

Strip the retail costume and this is a weighted average hiding behind a catalog query. 'Average unit price' is not the mean of each sale's own unit price; it is total dollars over total units, SUM(total_amount) / SUM(quantity). Anyone can join the two tables and group by product. What separates candidates is seeing that a product with one five-unit order and one single-unit order cannot be averaged row by row without over-weighting the small order. Reach for AVG(total_amount / quantity) and every product with mixed order sizes drifts off its true unit economics, so the wrong products slip through the $100 gate.

Break down the requirements

Step 1: Join transactions to products

Inner-join transactions to products on product_id. The join is what guarantees 'has been bought': only products that appear in transactions survive it, so no separate count predicate is needed, and unsold products never show up as null-priced rows. Sales whose product_id is null match nothing and drop out too.

Step 2: Group by product

GROUP BY the product (product_id plus its product_name) to collapse all of a product's purchases into one row before the metric is computed.

Step 3: Weight the average, then filter

Compute the unit-weighted average as SUM(total_amount) / SUM(quantity): total dollars across all purchases divided by total units, not the average of per-sale unit prices. Put that same expression in HAVING ... >= 100 so only products clearing the $100 gate remain, then emit LOWER(product_name) and sort by product_id.

The solution

SELECT t.product_id,
       LOWER(p.product_name) AS product_name,
       SUM(t.total_amount) / SUM(t.quantity) AS avg_unit_price
FROM transactions t
JOIN products p ON t.product_id = p.product_id
GROUP BY t.product_id, p.product_name
HAVING SUM(t.total_amount) / SUM(t.quantity) >= 100
ORDER BY t.product_id

Wrong: average of ratios

AVG(total_amount / quantity) averages each sale's own per-unit price equally, so a one-unit impulse buy counts as much as a five-unit bulk order. Products with lumpy order sizes report a price that matches none of their sales.

Right: ratio of sums

SUM(total_amount) / SUM(quantity) pools every dollar and every unit first, then divides once. Big orders pull the average in proportion to how much they actually sold, which is the unit economics the $100 gate is meant to measure.

Common follow-up questions

• The `rating` column in `products` has missing values. If a later version of this query filtered or averaged `rating`, how would NULLs change the result? (Tests whether the candidate accounts for NULLs in `products.rating` and understands how aggregates skip NULL values.)
• `transactions` has 80,000,000 rows keyed to only 30,000 products. What index would you build to avoid a full scan when computing this per-product metric? (Tests indexing knowledge for the `product_id` join key on the 80M-row fact table.)
• If the $100 threshold became 'top 10% of products by unit price' instead of a fixed number, how would you restructure the query? (Tests replacing a static `HAVING` threshold with a data-driven cutoff.)