Regional Status

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L3

Problem

The operations team is diagnosing fulfillment bottlenecks across regions. For each region and order status combination that has valid data on both fields, show the number of orders. Only surface combinations that account for at least three orders, and rank the results from the most common combination to the least.

The full regional breakdown.

Tables

orders

Columns: order_id INTEGER, status TEXT, region TEXT, profit REAL

orders: 10 of 200 rows
order_id	status	region	profit
10097	Pending	EU	69.37
10194	Cancelled	APAC	88.74
10291	Shipped	LATAM	108.11
10388	Processing	MEA	127.48
10485	Returned	US	146.85
10582	On Hold	EU	166.22
10679	Completed	APAC	185.59
10776	Pending	LATAM	204.96
10873	Cancelled	MEA	224.33
10970	Shipped	US	243.7

Expected output

First 5 of 33 rows
region	status	order_count
APAC	Cancelled	6
EU	Cancelled	6
LATAM	Cancelled	6
MEA	Completed	6
US	Pending	6

Worked solution and explanation

Why this problem exists in real interviews

Group by two columns, filter the groups by size, sort with a multi-key tiebreaker: this is the HAVING + ORDER BY interview question, and it covers three specific SQL skills in one problem. Every BI dashboard is built on queries of this shape. Interviewers grade whether you know HAVING vs WHERE (WHERE filters rows, HAVING filters groups), whether you handle NULLs explicitly, and whether you spell out the tiebreak columns in the exact order the spec dictates.

Break down the requirements

Step 1: Filter out NULL region or status before grouping

WHERE region IS NOT NULL AND status IS NOT NULL applies to rows, not groups, so it belongs in WHERE. Doing it in HAVING would still work because COUNT excludes NULLs per-column, but the NULL group itself would appear in output unless filtered. WHERE is the honest expression: 'drop bad rows first.'

Step 2: Group by (region, status)

GROUP BY region, status produces one row per distinct pair. COUNT(*) in the SELECT list tallies rows per group.

Step 3: Filter groups with HAVING

HAVING COUNT(*) >= 3 drops pairs with fewer than 3 rows. HAVING is the group-level filter; WHERE cannot reference aggregate values, so HAVING is the only place this filter can live.

Step 4: Sort by count DESC, then region ASC, then status ASC

ORDER BY order_count DESC, region ASC, status ASC. The spec is explicit about the tiebreak chain. ASC is default but spelling it out makes the intent self-documenting and survives a copy-paste into a different engine with different defaults.

The solution

WHERE for rows, HAVING for groups, explicit tiebreak

SELECT
    region,
    status,
    COUNT(*) AS order_count
FROM orders
WHERE region IS NOT NULL AND status IS NOT NULL
GROUP BY region, status
HAVING COUNT(*) >= 3
ORDER BY order_count DESC, region ASC, status ASC

Common follow-up questions

What changes if the spec wanted only the single highest-count row (with ties broken by region, status)? (add LIMIT 1; or use a window function with `DENSE_RANK` and filter to rank = 1. Discuss the difference when ties should all survive.)
How would you also return the total revenue per group? (add SUM(profit) AS `total_profit`; discuss NULL handling in SUM (skipped, not treated as zero).)
How would you rewrite this without HAVING? (a subquery that computes counts first, then a WHERE on the outer query; show why this is logically identical but sometimes optimized differently.)