# Top Region by Order Volume

> The single busiest region.

Canonical URL: <https://datadriven.io/problems/top_region_by_order_volume>

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Which single region generates the most orders? Return the region and its order count.

## Worked solution and explanation

### Why this problem exists in real interviews

The `orders` table is the foundation for this filtering to the top rows after aggregation problem. It tests whether you can compose a CTE or subquery that aggregates before ranking, then filter to the desired slice.

---

### Break down the requirements

#### Step 1: Aggregate per region

`GROUP BY region` with the appropriate aggregate function produces one summary row per group from the `orders` table.

#### Step 2: Rank the results

`ORDER BY` the aggregate descending with `LIMIT` to surface the top entries.

---

### The solution

**Count orders per region and pick the highest-volume one**

```sql
SELECT
    region,
    SUM(profit) AS total_profit
FROM orders
GROUP BY region
ORDER BY total_profit DESC
LIMIT 10
```

> **Cost Analysis**
>
> The GROUP BY reduces the 25M-row `orders` table to the number of distinct `region` values. A covering index on `(region, profit)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers verify you aggregate before sorting. Sorting raw rows gives per-row values, not group totals. The correct grain is one row per `region`.

> **Common Pitfall**
>
> Using the wrong aggregate function. `SUM` gives totals, `COUNT` gives volume, `AVG` gives rates. Read the prompt to determine which metric is needed.

---

## Common follow-up questions

- If two regions tie for the most orders, does LIMIT 1 return a deterministic result? _(Tests non-determinism; adding ORDER BY region as a tiebreaker stabilizes the output.)_
- Should you count all orders or only those with a specific status like 'completed'? _(Tests prompt interpretation; 'most orders' without a status filter means COUNT(*) over all rows.)_
- How would you also show the runner-up region in the same result set? _(Tests LIMIT 2 or DENSE_RANK with rank <= 2 approach.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_region_by_order_volume)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.