Repair Category Summary Grain
Repair it to return order count and revenue per stable category key.
- Joins
- Aggregation
- Filtering
- Sorting
- Distinct values
Exercise brief
Understand the request
Analytics quality reviewer A category summary selects product_name while grouping only by the non-unique category_name.
Repair a grouped item-revenue query that selects a row-level product attribute and groups by a non-unique label.
Return
- Return category_key, category_name, order_count, item_revenue in this exact left-to-right order.
Constraints
- Project and group by category_key and category_name; remove product_name.
- Exclude NULL quantity or unit_price; retain zero price.
- Count distinct orders and sort by category_key.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
products
product_idINTEGERcategory_keyINTEGERcategory_nameVARCHAR(80)product_nameVARCHAR(120)
order_items
order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceDECIMAL(10,2)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
At category grain, every selected expression must be a category attribute or an aggregate over item facts.
Hint 2
Remove product_name, keep the stable category key with its label, and filter incomplete item measures before grouping.
Hint 3
SELECT p.category_key, p.category_name, COUNT(DISTINCT /* order key */) AS order_count, SUM(/* quantity times price */) AS item_revenue FROM /* product-to-item population */ WHERE /* complete item measures */ GROUP BY /* stable category grain */ ORDER BY p.category_key;
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT p.category_key, p.category_name, COUNT(DISTINCT i.order_id) AS order_count, SUM(i.quantity * i.unit_price) AS item_revenue FROM products p INNER JOIN order_items i ON i.product_id = p.product_id WHERE i.quantity IS NOT NULL AND i.unit_price IS NOT NULL GROUP BY p.category_key, p.category_name ORDER BY p.category_key;Why this works
Correctness: every selected field is either part of the stable category grain or an aggregate over complete item facts. Edge case: category_name is deliberately duplicated across keys, and a permissive engine may return an arbitrary product_name when it is selected outside the group. Portability: strict engines reject the broken selected-column contract; SQLite can be permissive, so the repaired query must satisfy grain explicitly.
Success check
Each category key appears once with grain-valid metrics.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| category_key | category_name | order_count | item_revenue |
|---|---|---|---|
| 10 | Analytics | 4 | 590 |
| 20 | Storage | 2 | 160 |
| 30 | Accessories | 3 | 180 |
| 31 | Accessories | 2 | 200 |
| 40 | Services | 1 | 50 |
| 50 | Promotions | 1 | 0 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Finding Duplicates & Data Quality
Detect identity collisions, profile NULL-aware conflicts, and compare deterministic survivors with production-safe SQL.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
Open the interactive workspace and practice across SQL topics.