The Above Average

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

Problem

The pricing team is flagging products that look overpriced relative to their own category. For every product whose listed price exceeds the average price within its category, return the product name, category, price, and that category's average price.

What counts as good depends on the company you keep.

Tables

products

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

Expected output

Worked solution and explanation

The trap

This is a peer-group comparison dressed up as a pricing audit: every product is judged against its own category, not the catalog. Anyone can compute an average. What separates candidates is getting that average back onto each individual row at the right scope. Reach for one storewide AVG(price) and the bar sits near 897, so 'Ultra Tool 14X' at 115.41 never gets flagged even though its two-item 'Collectibles' category averages 77.76. Meanwhile 'Essential Set v4' gets flagged at 969.80 while sitting under its 'Sports' average. Try WHERE price > AVG(price) instead and the query does not even run, because a row filter cannot see an aggregate.

Building it

Step 1: Compute each category's average once

Collapse products to one row per category with AVG(price) AS avg_price in a CTE named category_avgs. This is the only place rows get folded: eleven categories, eleven averages. AVG skips the NULL prices, and the 0.0 free sample in 'Beauty' counts as a real price, which is what a pricing team means by average.

Step 2: Join every product back to its category

Join products to category_avgs on category. Each product picks up exactly one avg_price because the CTE holds one row per category, so nothing fans out. Every product has a category, so the inner join loses nobody.

Step 3: Filter on the row, strictly

Now p.price > ca.avg_price is an ordinary row predicate, so it belongs in WHERE. Strict greater-than: a product sitting exactly on its category average is not overpriced. A NULL price compares as unknown and drops out on its own.

Step 4: Project four columns, no sort

Return product_name, category, price and avg_price. Nothing in the ask is about order, so there is no ORDER BY for the grader to hold you to.

WITH category_avgs AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
)
SELECT
    p.product_name,
    p.category,
    p.price,
    ca.avg_price
FROM products p
JOIN category_avgs ca ON p.category = ca.category
WHERE p.price > ca.avg_price

SELECT
    product_name,
    category,
    price,
    (SELECT AVG(price) FROM products) AS avg_price
FROM products
WHERE price > (SELECT AVG(price) FROM products)

Common follow-up questions

• Rewrite this with `AVG(price) OVER (PARTITION BY category)` instead of a CTE. (Tests knowing that a window result cannot be filtered in the same `SELECT`'s `WHERE`, so it needs a wrapping query.)
• What changes if you also need how far above average each product is, as a percentage? (Tests dividing by `avg_price`, scaling to a percentage and deciding on rounding.)
• How would you exclude the product itself from its category's average? (Tests leave-one-out: `(SUM(price) - price) / (COUNT(price) - 1)`, and what a one-product category should do.)
• When would the storewide average be the right threshold instead? (Tests naming the business meaning of each scope, not just the syntax.)