SQL Aggregations SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Regional Category Order Mix

Return one order-count and revenue row per observed region-category key pair.

  • Joins
  • Aggregation
  • Filtering
  • Sorting
  • Distinct values

Exercise brief

Understand the request

Merchandising analytics manager Assortment planning needs a stable regional category view without merged display labels.

Return one order and item-revenue row per observed region and product-category pair.

Return

  • Return region_id, region_code, category_key, category_name, order_count, item_revenue in this exact left-to-right order.

Constraints

  • Group by both stable keys and their display fields.
  • Exclude NULL quantity or unit_price; retain zero price.
  • Count distinct orders; sum quantity * unit_price; order by both keys.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

regions

  • region_idINTEGER
  • region_codeVARCHAR(30)

orders

  • order_idINTEGER
  • region_idINTEGER

order_items

  • order_idINTEGER
  • product_idINTEGER
  • quantityINTEGER
  • unit_priceDECIMAL(10,2)

products

  • product_idINTEGER
  • category_keyINTEGER
  • category_nameVARCHAR(80)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The report has two business dimensions: stable region and stable category.

Hint 2

Join orders to item facts and both dimensions, filter incomplete item measures, then group by both keys and their display attributes.

Hint 3

SELECT r.region_id, r.region_code, p.category_key, p.category_name, COUNT(DISTINCT /* order key */) AS order_count, SUM(/* quantity times price */) AS item_revenue FROM /* order, item, region, and product joins */ WHERE /* complete item measures */ GROUP BY /* both stable dimensions */ ORDER BY r.region_id, p.category_key;

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT r.region_id, r.region_code, p.category_key, p.category_name, COUNT(DISTINCT o.order_id) AS order_count, SUM(i.quantity * i.unit_price) AS item_revenue FROM orders o INNER JOIN regions r ON r.region_id = o.region_id INNER JOIN order_items i ON i.order_id = o.order_id INNER JOIN products p ON p.product_id = i.product_id WHERE i.quantity IS NOT NULL AND i.unit_price IS NOT NULL GROUP BY r.region_id, r.region_code, p.category_key, p.category_name ORDER BY r.region_id, p.category_key;

Why this works

Correctness: region and category keys establish a two-dimensional business grain, while COUNT(DISTINCT order_id) prevents multiple item rows from overstating orders. Edge case: category_name is deliberately duplicated across category keys, zero price remains valid, and incomplete item measures are excluded. Portability: multi-column GROUP BY and single-expression COUNT(DISTINCT) are supported across the live engines.

Success check

Every observed region-category key pair appears exactly once with correct metrics.

Expected result

Use this output to verify values, aliases, ordering, and row count.

region_idregion_codecategory_keycategory_nameorder_countitem_revenue
1NORTHEAST10Analytics1190
1NORTHEAST20Storage1100
1NORTHEAST30Accessories190
2NORTHWEST10Analytics160
2NORTHWEST30Accessories160
2NORTHWEST31Accessories1120
2NORTHWEST50Promotions10
3SOUTHEAST10Analytics1240
3SOUTHEAST30Accessories130
4WEST10Analytics1100

Previewing 10 of 13 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.