Brightlane's pricing analyst wants to identify products that are priced above the average for their own category (not the catalogue-wide average).
Write a query to return the product name and price for every product that exceeds its own category's average price.
Assumptions:
- The
productstable contains every product in the catalogue. - The threshold (each category's average) varies by category — the comparison applies the category-specific average, not a single catalogue-wide value.
- A product priced exactly at its category's average does not qualify (strictly greater than).
Output:
- One row per qualifying product, with columns
nameandprice.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
p.name,
p.price
FROM
products p
WHERE
p.price > (
SELECT
AVG(p2.price)
FROM
products p2
WHERE
p2.category_id = p.category_id
)The shape
The filter threshold isn't one number; it's a per-row number. For each product, the subquery computes the average price across that product's own category, and the outer WHERE compares the product's price against that category-specific value. Apex Titan 15 is compared against 782.33; Crest Pro 14" is compared against 1459. Different rows, different thresholds, one query.
Clause by clause
FROM products pis the outer source. The aliaspis what the subquery refers back to.- The correlated subquery inside
WHERE:
(SELECT AVG(p2.price)
FROM products p2
WHERE p2.category_id = p.category_id)p2 is a second alias on the same products table, so the two references stay distinct. The inner query reads every product in the same category as the current outer row and averages their prices. The outer query supplies the category_id value; the subquery resolves it to a single average.
- WHERE p.price > (...) then compares the outer row's price to the category-specific average. Rows at or below their own category's average drop out; rows strictly above pass.
- SELECT p.name, p.price returns the two columns the pricing analyst needs to identify each premium-tier product.
Why this and not the catalogue-wide average
The simpler shape — WHERE price > (SELECT AVG(price) FROM products) — compares every product against a single global threshold of 326.58. That mixes categories: a 12.99 HDMI cable is compared against the same threshold as a 1999 laptop, and the result skews toward high-priced categories. The correlated form gives each product a fair, like-for-like comparison against its peers.
The trap
Products with category_id as NULL silently disappear from the result. For the gift cards and the mystery bundle, the subquery's filter becomes WHERE p2.category_id = NULL, which never matches — so the inner query returns zero rows, the subquery resolves to NULL, and price > NULL evaluates to unknown. Rows with unknown filter conditions don't pass WHERE. The query runs without error, and entire categories of products are excluded from the result without warning. Any time a correlated key is nullable, the rows where it's missing fall out of the answer.
You practiced a correlated scalar subquery as a WHERE threshold. The recurring rule: the subquery references the outer row's category_id, so the threshold computed for each row is specific to that row's category — different rows are filtered against different thresholds in a single query.
