SQL Aggregations SQL Topic exerciseHardVerified answerPostgreSQL live

Regional Revenue Rollup

Return detail rows per region-category key, one subtotal per represented region, and one company grand total.

  • Joins
  • Subqueries
  • Aggregation
  • CASE expressions
  • NULL handling

Exercise brief

Understand the request

FP&A reporting director The planning workbook needs category detail, regional subtotals, and one company total without confusing source NULLs with generated rollup rows.

Produce governed detail, regional subtotal, and company-total revenue rows with grouping metadata.

Return

  • Return region_id, region_label, category_key, category_label, group_level, recognized_revenue in this exact left-to-right order.

Constraints

  • Use PostgreSQL GROUPING SETS and GROUPING metadata; do not infer subtotal rows by testing a dimension for NULL.
  • Exclude CANCELLED orders and item rows whose quantity or unit_price is NULL; retain valid zero revenue.
  • Keep stable region_id and category_key values in detail rows so duplicate display labels remain distinct.
  • Label a genuine NULL region_name as UNNAMED REGION and generated totals as ALL REGIONS or ALL CATEGORIES.
  • Sort detail before its regional subtotal and place the company total last.

Data you will use

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

regions

  • region_idINTEGER
  • region_nameVARCHAR(80)

products

  • product_idINTEGER
  • category_keyINTEGER
  • category_nameVARCHAR(80)

orders

  • order_idINTEGER
  • region_idINTEGER
  • statusVARCHAR(30)

order_items

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

Hints, when you need them

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

Hint 1

A subtotal NULL is generated by the grouping operator; a source NULL is data. GROUPING tells those cases apart.

Hint 2

Normalize valid item revenue to region-category grain, then request only detail, region, and company grouping sets.

Hint 3

WITH item_facts AS (/* one row per stable region-category key */) SELECT /* stable keys and GROUPING-aware labels */, SUM(/* recognized measure */) FROM item_facts JOIN regions r ON /* stable key */ GROUP BY GROUPING SETS ( (/* detail keys */), (/* regional subtotal keys */), () ) ORDER BY /* grouping level and stable keys */;

Verified SQL answer

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

Reveal solution and explanation
WITH item_facts AS (SELECT o.region_id, p.category_key, p.category_name, SUM(oi.quantity * oi.unit_price) AS recognized_revenue FROM orders o INNER JOIN order_items oi ON oi.order_id = o.order_id INNER JOIN products p ON p.product_id = oi.product_id WHERE o.status <> 'CANCELLED' AND oi.quantity IS NOT NULL AND oi.unit_price IS NOT NULL GROUP BY o.region_id, p.category_key, p.category_name) SELECT r.region_id, CASE WHEN GROUPING(r.region_id) = 1 THEN 'ALL REGIONS' ELSE COALESCE(r.region_name, 'UNNAMED REGION') END AS region_label, f.category_key, CASE WHEN GROUPING(f.category_key) = 1 THEN 'ALL CATEGORIES' ELSE f.category_name END AS category_label, CASE WHEN GROUPING(r.region_id) = 1 THEN 'company_total' WHEN GROUPING(f.category_key) = 1 THEN 'region_total' ELSE 'detail' END AS group_level, SUM(f.recognized_revenue) AS recognized_revenue FROM item_facts f INNER JOIN regions r ON r.region_id = f.region_id GROUP BY GROUPING SETS ((r.region_id, r.region_name, f.category_key, f.category_name), (r.region_id, r.region_name), ()) ORDER BY GROUPING(r.region_id), r.region_id, GROUPING(f.category_key), f.category_key;

Why this works

Correctness: explicit grouping sets emit only the requested detail, regional subtotal, and company grains, and GROUPING metadata labels generated totals. Edge case: region 6 has a genuine NULL name and category keys 30 and 31 share one label, so NULL tests and display-label grouping are unsafe. Portability: this contract executes natively in PostgreSQL; SQL Server and Oracle have guided GROUPING SETS variants, MySQL uses a guided ROLLUP hierarchy, and SQLite requires a UNION ALL fallback that does not assess the same construct.

Success check

All 12 detail rows, five regional subtotals, and one company total appear once and reconcile to 1150 recognized revenue.

Expected result

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

region_idregion_labelcategory_keycategory_labelgroup_levelrecognized_revenue
1North10Analyticsdetail190
1North20Storagedetail100
1North30Accessoriesdetail90
1NorthNULLALL CATEGORIESregion_total380
2North10Analyticsdetail60
2North30Accessoriesdetail60
2North31Accessoriesdetail120
2North50Promotionsdetail0
2NorthNULLALL CATEGORIESregion_total240
3Southeast10Analyticsdetail240

Previewing 10 of 18 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.