Regional Order Value Baseline
Return one order-volume and value row per region having orders.
- Joins
- Aggregation
- HAVING
- Numeric functions
- Sorting
Exercise brief
Understand the request
Commercial analytics lead The regional baseline must remain stable when display labels are duplicated or missing.
Return one order-volume and value summary row per stable reporting-region key.
Return
- Return region_id, region_code, total_orders, valued_orders, total_order_value, average_order_value in this exact left-to-right order.
Constraints
- Group by region_id and region_code, not the non-unique region_name.
- Count all orders separately from non-NULL order_amount values.
- Round average_order_value to 2 decimal places and retain zero.
- Sort by region_id ascending.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
regions
region_idINTEGERregion_codeVARCHAR(30)
orders
order_idINTEGERregion_idINTEGERorder_amountDECIMAL(12,2)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Choose the stable region key as the reporting grain before adding measures.
Hint 2
Join regions to orders, group once by region_id and region_code, then calculate row and value metrics in the same SELECT.
Hint 3
SELECT r.region_id, r.region_code, COUNT(/* order key */) AS total_orders, COUNT(/* nullable measure */) AS valued_orders, SUM(/* measure */) AS total_order_value, ROUND(AVG(/* measure */), 2) AS average_order_value FROM /* region-to-order population */ GROUP BY /* stable region key and code */ ORDER BY r.region_id;
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, COUNT(o.order_id) AS total_orders, COUNT(o.order_amount) AS valued_orders, SUM(o.order_amount) AS total_order_value, ROUND(AVG(o.order_amount), 2) AS average_order_value FROM regions r INNER JOIN orders o ON o.region_id = r.region_id GROUP BY r.region_id, r.region_code ORDER BY r.region_id;Why this works
Correctness: one grouping operation computes order and value metrics at stable region-key grain. Edge case: grouping by region_name would merge the two different North keys, while COUNT(order_amount) intentionally differs from order count where an amount is NULL. Portability: multi-column GROUP BY and these aggregates are supported across the live engines; numeric result types and display scale can differ.
Success check
Each of five regions appears once with correct measures.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| region_id | region_code | total_orders | valued_orders | total_order_value | average_order_value |
|---|---|---|---|---|---|
| 1 | NORTHEAST | 3 | 3 | 380 | 126.67 |
| 2 | NORTHWEST | 4 | 3 | 240 | 80 |
| 3 | SOUTHEAST | 2 | 2 | 270 | 135 |
| 4 | WEST | 2 | 2 | 210 | 105 |
| 6 | UNASSIGNED | 1 | 1 | 80 | 80 |
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.