Top Region by Order Volume

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Forbes

Problem

We run an online marketplace and want to spotlight our quietest region: the one handling the fewest orders, ignoring any orders that were never tagged to a region. Return that region and its order count, and when several regions tie for the fewest, report the one whose name sorts last alphabetically.

The single busiest region.

Tables

orders

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

Expected output

Worked solution and explanation

What this problem is really testing

This is deterministic single-extreme selection dressed up as marketplace analysis. Anyone can GROUP BY region and COUNT(*) to get a total for every region. The real skill is collapsing that down to the one row you want, past two quiet traps: the untagged orders where region IS NULL clump into their own group of 18, smaller than any real region's 36, so leaving them in hands back a null 'region'; and the four real regions all tie at 36, so LIMIT 1 with no secondary sort returns whichever one the engine surfaces first. Miss either and the query runs clean and disagrees with the expected MEA.

Break down the requirements

Step 1: Drop the untagged orders

Filter with WHERE region IS NOT NULL before grouping. The 18 orders that were never tagged to a region would otherwise form the smallest group of all and win, so dropping them is what keeps the answer a real region.

Step 2: Count orders per region

Group by region and count rows with COUNT(*). That collapses the table to one row per region, each carrying its order total.

Step 3: Sort the fewest to the top, then break the tie

Add ORDER BY order_count ASC, region DESC. The ascending count puts the fewest first; the region DESC tie-break settles the four-way tie at 36 so exactly one region survives, the one whose name sorts last.

Step 4: Keep only the winner

Take LIMIT 1 to keep just that top row. It is meaningful only because the two-key sort above gives a total ordering; without the tie-break this would be arbitrary.

The solution

SELECT region, COUNT(*) AS order_count
FROM orders
WHERE region IS NOT NULL
GROUP BY region
ORDER BY order_count ASC, region DESC
LIMIT 1;

Common follow-up questions

• What would you change if a tie should instead go to the region with the highest total profit? (Tests swapping the `region DESC` tie-break for a `SUM(profit) DESC` secondary sort while keeping the same shape.)
• How would the query change if 'quietest' meant lowest total profit instead of fewest orders? (Tests replacing `COUNT(*)` with `SUM(profit)` while keeping the selection shape identical.)
• If you wanted the full standing of every region by order volume, what would you reach for? (Tests reaching for `RANK()` or `DENSE_RANK()` window functions over the grouped counts.)