# The Premium Shelf > The catalog's high end, weighed one unit at a time. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem Find products with at least 1 purchase and a unit-weighted average price (total amount divided by quantity) of at least $100. Pull the product name from the products table and return it in lowercase. Return the product ID, product name (lowercased), and average unit price. ## 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. The metric 'average unit price' is NOT the average of each transaction's per-row unit price; it is total dollars divided by 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 big five-unit order and one tiny 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. This join is what guarantees 'at least 1 purchase': only products that actually appear in the transactions table survive it, so no separate count predicate is needed, and unpurchased products never show up as null-priced rows. #### Step 2: Group by product GROUP BY the product (product_id plus its 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-transaction unit prices. Apply that same expression in HAVING >= 100 so only products clearing the $100 unit-price gate remain. Then LOWER(product_name) for the lowercased name and ORDER BY product_id. --- ### The solution **Weighted unit price with a HAVING gate** ```sql 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 transaction's own per-unit price equally, so a one-unit impulse buy counts as much as a fifty-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 real unit economics the $100 gate is meant to measure. > **Cost at scale** > > With transactions at 80,000,000 rows and products at 30,000, the cost is dominated by scanning and aggregating the fact table. A covering index on (product_id, quantity, total_amount) lets the group-and-sum run off the index; at this scale a pre-aggregated rollup keyed by product_id turns the interactive query into a lookup. > **Interviewers watch for** > > The tell here is whether you divide sums or average ratios. Strong candidates state the weighting choice out loud before writing the metric, and confirm that the same expression drives both the SELECT and the HAVING so the reported price and the filter can never disagree. > **Common pitfall** > > Beyond the averaging trap, the classic slip is putting the price threshold in WHERE instead of HAVING. The metric is an aggregate, so it does not exist until after grouping; a WHERE on it either errors or silently filters raw rows. --- ## Common follow-up questions - The rating column in products has a 5% null rate. 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 specific to the high-cardinality 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 ability to replace static HAVING filters with dynamic subquery-based thresholds.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_premium_shelf) - [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.