SolveWithSQL

SQL Intermediate Challenge 1: Above-Average Products per Category (Subquery / CTE)

Difficulty: Intermediate (early)
Estimated time: 20–30 minutes
SQL concepts: subqueries or CTEs, AVG(), GROUP BY, joining aggregated results
Goal: Compare row-level values to a group-level benchmark.

The Challenge

The product manager asks:

“Which products are priced above the average of their own category?”

This is a classic analytics question: you need a benchmark per category (the average), then you compare each product against that benchmark.

Database Table

products

column_nametypedescription
product_idINTEGERUnique product ID
nameTEXTProduct name
categoryTEXTProduct category
priceDECIMALProduct price

Sample Data

product_idnamecategoryprice
101Wireless MouseAccessories24.99
102Mechanical KeyboardAccessories89.00
103WebcamAccessories59.99
104USB-C HubAccessories34.50
105MonitorDisplays229.99
10634-inch MonitorDisplays399.00
107Laptop StandWorkspace39.99
108Chair MatWorkspace79.99

Your Task

Return all products where:

  • price is greater than the average price of that product’s category

Your output should include:

  • category
  • name
  • price
  • category_avg_price

Sort results by:

  1. category (A–Z)
  2. price (high to low)

Expected Output (based on sample data)

Category averages (approx):

  • Accessories avg = (24.99 + 89.00 + 59.99 + 34.50) / 4 = 52.12
  • Displays avg = (229.99 + 399.00) / 2 = 314.50
  • Workspace avg = (39.99 + 79.99) / 2 = 59.99

Above-average products:

  • Accessories: Mechanical Keyboard (89.00), Webcam (59.99)
  • Displays: 34-inch Monitor (399.00)
  • Workspace: Chair Mat (79.99)
categorynamepricecategory_avg_price
AccessoriesMechanical Keyboard89.0052.12
AccessoriesWebcam59.9952.12
Displays34-inch Monitor399.00314.50
WorkspaceChair Mat79.9959.99

Constraints

  • Use either:
    • a CTE (recommended), or
    • a subquery/derived table
  • Do not use SELECT *
  • Ensure the average is calculated per category
  • Include the average value in the results

Hint (Optional)

You can’t compare a product to its category average unless you first compute that category average somewhere.

Think:

  1. Build a table of category averages
  2. Join it back to products
  3. Filter where product price > category average

WITH category_avgs AS (
SELECT
category,
AVG(price) AS category_avg_price
FROM products
GROUP BY category
)
SELECT
p.category,
p.name,
p.price,
ROUND(ca.category_avg_price, 2) AS category_avg_price
FROM products p
JOIN category_avgs ca
ON p.category = ca.category
WHERE p.price > ca.category_avg_price
ORDER BY
p.category ASC,
p.price DESC;

Explanation

  • The CTE category_avgs computes one row per category with its average price.
  • We join that back to products so each product row “knows” its category average.
  • The WHERE clause filters to products priced above that average.
  • The final ORDER BY ensures consistent, readable output.

This pattern (benchmark + compare) is extremely common in:

  • pricing analytics
  • performance vs baseline reporting
  • anomaly detection
  • segmentation

Common Mistakes

  • Computing a single overall average instead of per-category averages
  • Trying to use AVG(price) directly in WHERE without a subquery/CTE
  • Forgetting to join category averages back to products
  • Sorting without a tie-breaker or consistent ordering

Optional Extension (Mini Bonus)

  1. Return products that are 10% above their category average
  2. Add a computed column:
    • difference = price - category_avg_price
  3. Show only categories where at least 2 products are above average
    (you’ll likely need another grouping step)

🔗 Next Intermediate Challenge

Intermediate Challenge #2: Top Product per Category (Using ROW_NUMBER() or a correlated subquery)

🔗 View reference solution on GitHub
(After you’ve tried the challenge)

Want more practical SQL challenges?
Subscribe to the Solve With SQL newsletter and get new problems delivered to your inbox.