Back to Practice
#0335
Category Winner With FIRST_VALUE
MediumSQL24 min20 XP
Problem
Return category, product, revenue, and top_product for every product.
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
sales(category TEXT, product TEXT, revenue INTEGER)
Examples
Example 1
InputPython has highest Books revenue
Outputtop_product Python on Books rows
FIRST_VALUE reads the first product in each revenue-ordered category partition.
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
InputPython has highest Books revenue
Expected[["Books","Python",100,"Python"],["Books","SQL",90,"Python"],["Tools","Mouse",25,"Mouse"]]
FIRST_VALUE reads the first product in each revenue-ordered category partition.
Hidden Test Categories
Empty or missing matchesDuplicate rows or tiesNULL values where the pattern commonly breaks