SolveWithSQL

Difficulty: Intermediate
Estimated time: 35–50 minutes
SQL concepts: median logic, COUNT(), ORDER BY, LIMIT/OFFSET, correlated subqueries, joining benchmarks
Goal: Compute a median per category and filter products above it.

The Challenge

The product manager asks:

“Which products are priced above the median price of their category?”

You already solved “above category average.”
Median is harder because SQL does not have a universal built-in median function, and you must define the rules.

Median rule for this challenge (required)

Within each category, sort products by:

1. price ASC
2. product_id ASC (tie-breaker)

Then:

• If there are odd number of products → median is the middle value
• If there are even number of products → median is the average of the two middle values

This is the most common “statistics median” definition and works well for analytics.

Database Table

products

column_name	type	description
product_id	INTEGER	Unique product ID
name	TEXT	Product name
category	TEXT	Product category
price	DECIMAL	Product price

Sample Data (Accessories has an even count)

product_id	name	category	price
301	Wireless Mouse	Accessories	24.99
302	USB-C Hub	Accessories	34.50
303	Webcam	Accessories	59.99
304	Mechanical Keyboard	Accessories	89.00
305	Monitor	Displays	229.99
306	34-inch Monitor	Displays	399.00
307	Desk Lamp	Workspace	49.99
308	Chair Mat	Workspace	79.99
309	Standing Desk	Workspace	299.00

Accessories sorted prices: 24.99, 34.50, 59.99, 89.00
Median = (34.50 + 59.99) / 2 = 47.245

Your Task

Return products where:

• price is greater than the median price of their category

Your output must include:

• category
• name
• price
• category_median_price

Sort results by:

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

Expected Output (based on sample data)

Products above median:

• Accessories: 59.99, 89.00 (median ≈ 47.25)
• Displays: 399.00 (median = (229.99+399.00)/2 = 314.495)
• Workspace: 299.00 (sorted: 49.99, 79.99, 299.00 → median = 79.99)

category	name	price	category_median_price
Accessories	Mechanical Keyboard	89.00	47.25
Accessories	Webcam	59.99	47.25
Displays	34-inch Monitor	399.00	314.50
Workspace	Standing Desk	299.00	79.99

Constraints

• Do not use SELECT *
• Median must follow the rule defined above
• Median must be calculated per category
• Include category_median_price in the output

Hint (Optional)

A practical way to compute median per category (SQLite-friendly) is:

1. Count how many rows are in the category (cnt)
2. Sort the category’s prices
3. Take the middle 1 or 2 rows using LIMIT 2 OFFSET (cnt - 1) / 2
4. Take the AVG() of those 1–2 rows → that’s the median

Explanation

• category_medians builds one row per category with its median price.
• The inner “middle” subquery:
  • sorts prices within a category
  • uses OFFSET (cnt - 1) / 2 to jump to the middle
  • uses LIMIT 2 to grab the middle one (odd) or middle two (even)
  • averages them to produce the median
• Then we join medians back to products and filter products above median.

🔑 Key Insight

Median requires positional logic.
Averages are easy because they’re purely mathematical; median depends on ordered position within each group.

Common Mistakes

• Using AVG() instead of median (different metric)
• Forgetting a tie-breaker in ordering (unstable medians)
• Using LIMIT/OFFSET without counting per category
• Computing one global median instead of per-category medians

Optional Extension (Mini Bonus)

1. Return products at or above the median (use >=)
2. Add a column: difference_from_median = price - median
3. Return categories where more than 50% of products are above the median (requires more steps)

🔗 Next Intermediate Challenge

Intermediate Challenge #5: Running Totals per Category (intro to analytics-style cumulative metrics)

🔗 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.