Regional Order Coverage
Return one order-count row per region, including regions with zero orders.
- Joins
- Aggregation
- Sorting
Exercise brief
Understand the request
Regional operations lead Coverage monitoring must retain every governed region even when no order facts arrived for it.
Return one order-count row for every reporting region, including regions with no orders.
Return
- Return region_id, region_code, order_count in this exact left-to-right order.
Constraints
- Preserve every region_id and region_code from the region dimension.
- Count matched order_id values so an empty region contributes 0 rather than 1.
- 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_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Start from the complete regions dimension, then attach any matching order facts.
Hint 2
A LEFT JOIN preserves empty regions; count the nullable matched order key, not the joined row.
Hint 3
SELECT r.region_id, r.region_code, COUNT(/* matched fact key */) AS order_count FROM regions r LEFT JOIN orders o ON /* region relationship */ 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 order_count FROM regions r LEFT 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: starting from regions and left joining orders preserves the full dimension, while COUNT(order_id) ignores the unmatched NULL row. Edge case: COUNT(*) would incorrectly report one order for CENTRAL, and an INNER JOIN would remove CENTRAL entirely. Portability: LEFT JOIN plus COUNT(non-null fact key) is portable across the supported engines.
Success check
All six regions are returned exactly once, including CENTRAL with an order_count of 0.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| region_id | region_code | order_count |
|---|---|---|
| 1 | NORTHEAST | 3 |
| 2 | NORTHWEST | 4 |
| 3 | SOUTHEAST | 2 |
| 4 | WEST | 2 |
| 5 | CENTRAL | 0 |
| 6 | UNASSIGNED | 1 |
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.