Back to Practice
#0381
Difference From Category Average
MediumSQL24 min20 XP
Problem
Return product, category, price, and diff_from_category_avg.
Why This Matters
SQL interviews usually test whether you can turn a business question into the right rows, grouping level, join type, and edge-case behavior.
Schema
products(product TEXT, category TEXT, price REAL)
Examples
Example 1
Inputcategory A average is 20
Outputp1 -10, p2 +10
Window AVG preserves product rows while adding group context.
Constraints
- Return only the requested columns.
- Use deterministic ORDER BY when multiple rows are returned.
- Assume SQLite-compatible SQL unless the prompt states otherwise.
CodeSQL
Visible browser tests run here when available.
Testcases1 visible / 3 hidden categories
Core SQL case
Inputcategory A average is 20
Expected[["p1","A",10,-10],["p2","A",30,10],["q1","B",5,0]]
Window AVG preserves product rows while adding group context.
Hidden Test Categories
Empty or missing matchesDuplicate rows or tiesNULL values where the pattern commonly breaks