Regional Profits

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

Orders that were never tagged with a region have no place in a regional P&L, so the analyst building the quarterly deck wants them kept out of the breakdown of the orders table. For each region that remains, show its total profit and its number of orders, highest total profit first.

P&L by region. Before the board meeting.

Tables

orders

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

orders: 10 of 200 rows
order_idstatusregionprofit
10097PendingEU69.37
10194CancelledAPAC88.74
10291ShippedLATAM108.11
10388ProcessingMEA127.48
10485ReturnedUS146.85
10582On HoldEU166.22
10679CompletedAPAC185.59
10776PendingLATAM204.96
10873CancelledMEA224.33
10970ShippedUS243.7

Expected output

First 5 of 5 rows
regiontotal_profitorder_count
US31324.438
APAC27668.8836
EU26959.6436
LATAM26911.3236
MEA26620.5636

Worked solution and explanation

This is a filter-before-you-collapse problem wearing a P&L costume. Everyone writes SUM(profit) and COUNT(*) per region. What separates candidates is knowing that grouping does not skip a NULL key. Aggregates ignore NULL values, but GROUP BY collects every NULL region into one group of its own. Skip WHERE region IS NOT NULL and the deck grows a sixth line: a blank region carrying 17,076.30 of profit across 18 orders that belong to no market.

Three moves, in this order

Step 1: Drop untagged orders before grouping

18 of the 200 rows in orders have no region. Remove them in WHERE, while they are still rows, so they never form a group. HAVING region IS NOT NULL happens to run, but it says the wrong thing: these orders were never meant to be in the report, so remove them as orders rather than deleting a group after summing it.

Step 2: Collapse to one row per region

SUM(profit) gives the total and COUNT(*) gives the order count. Count rows, not a column: every order counts once whatever its status, and status is NULL on 14 of the remaining rows, so COUNT(status) would quietly undercount. The zero and negative profits are real P&L and stay in the sum.

Step 3: Sort on the computed total

ORDER BY total_profit DESC sorts on the aggregate's alias, so US at 31,324.40 leads and MEA closes the list. The five totals all differ, so no tiebreaker is needed.

Regional profit, untagged orders excluded
SELECT region, SUM(profit) AS total_profit, COUNT(*) AS order_count
FROM orders
WHERE region IS NOT NULL
GROUP BY region
ORDER BY total_profit DESC
The version that looks right
SELECT region, SUM(profit) AS total_profit, COUNT(*) AS order_count
FROM orders
GROUP BY region
ORDER BY total_profit DESC

Runs cleanly and returns six rows. The last has a NULL region, 17076.3 of profit and 18 orders.

What aggregates do with NULL
SUM(profit) and COUNT(profit) skip NULL values inside a group, so a missing number never moves a total.
What grouping does with NULL
GROUP BY region treats every NULL region as the same key and emits one group for all of them, so a missing label becomes a row.

Common follow-up questions