The Provinces

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

mid

Asked in

Snap

Problem

We run a retail operation split into regional zones, with the US as our home base and main branch. Find the three highest earning zones outside the home region, biggest earners first.

The home office isn't the whole story. Find the zones carrying the rest.

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 a top-three-after-exclusion dressed up as a regional earnings report. The skill being probed: do you drop the home region BEFORE you rank, or after? Anyone can sum profit per region. The tell is whether the filter that removes the main branch runs on the base rows, ahead of the ordering and the LIMIT. Rank first and slice off the top three and you either hand back the US home region you were told to exclude, or you silently bump a real zone off the bottom of the list.

Break down the requirements

Step 1: Exclude the home region on the base rows

'US as our home base and main branch' is a filter on region, not a row you remove after aggregating. Put WHERE region <> 'US' before the grouping so US profit never enters any bucket.

Step 2: Roll up to the zone grain

GROUP BY region and SUM(profit). The question is about zones, so one row per region is the unit. Note profit can be negative on returns, so SUM (not COUNT) is the earning measure.

Step 3: Order high to low, then take three

ORDER BY total_profit DESC puts the biggest earners on top, and LIMIT 3 keeps the leaderboard short. The DESC has to sit on the summed total, not on raw profit.

The solution

SELECT region,
       SUM(profit) AS total_profit
FROM orders
WHERE region <> 'US'
GROUP BY region
ORDER BY total_profit DESC
LIMIT 3

Exclude after ranking (wrong)

Sum every region including US, sort, take the top three, then try to drop US. If US is the biggest earner it was inside your top three, so you return two real zones plus a hole, or you keep US entirely.

Exclude before ranking (right)

WHERE region <> 'US' removes the home region from the base rows, so the aggregate, the sort, and the LIMIT all operate only on the zones you actually want. The top three are three genuine outside zones.

Common follow-up questions

• The main branch isn't fixed; it's whichever region earns the most. Exclude that one dynamically and return the next three. (Pushes toward a scalar subquery or a window that finds the max region total first, testing whether they can make the exclusion data-driven instead of hardcoded.)
• Two zones tie on total profit at the third spot. Which one does LIMIT 3 keep, and how would you make it deterministic? (Tests awareness that LIMIT without a full tiebreaker in ORDER BY is nondeterministic, and that a secondary sort key (region name) fixes it.)
• Now scope it to this fiscal year only. Where does the date predicate go and why? (Checks that they add the window to WHERE alongside the region filter so it prunes before aggregation, rather than filtering after the rollup.)