Lines on the Map

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

Problem

The ops team is preparing a regional performance recap from the orders log, ignoring any order with no region recorded. For each region whose orders averaged more than 740 in profit apiece, show the region, its order count, and its total profit.

Every region keeps its own ledger. The totals set the order.

Tables

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

orders

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

Expected output

Worked solution and explanation

What this really is

Strip the costume and this is a single-table aggregation with two quiet traps. The orders table already carries region and profit on every row, so the recap is a straight group-and-total over one table: the customers table dangling beside it shares no key with orders, so any join is a guess that matches nothing or inflates every count. The subtler trap is the filter itself. It is a per-order average, and an average does not exist until the rows are grouped, so it can only live in HAVING, never in WHERE. And the orders with no region recorded happen to carry the highest average of any group, so they sail straight past an average filter and become a phantom region unless you drop them first.

The phantom join

Guessing at a join

Joining ON orders.region = customers.country feels plausible because both sound geographic. But region values are 'EU', 'APAC', 'US' while country values are 'Italy', 'India', 'Spain'. They never match, so a LEFT JOIN pins a NULL customer onto every order and any customer count comes out 0 for all regions. Worse, if one region string ever equaled a country, the join would fan out order rows and inflate both COUNT(order_id) and SUM(profit).

Orders only

Every metric the recap asks for lives on orders: the region to group by, the order_id to count, the profit to sum and to average. No second table is needed, and the result is exactly what the expected preview shows: region, order count, total profit.

Building it

Step 1: Drop orders with no region

Rows where region IS NULL would collapse into one bucket, and here they carry the highest average profit of any group, so an average filter will NOT remove them. Drop them in WHERE, before grouping, so they never reach the totals or slip past the floor.

Step 2: Group by region and total each metric

One row per region. COUNT(order_id) gives the order count and SUM(profit) gives the total profit. Both are plain aggregates over the grouped rows.

Step 3: Gate the average with HAVING, not WHERE

The 'averaged more than 740 per order' rule tests an aggregate, and aggregates do not exist yet when WHERE runs. HAVING AVG(profit) > 740 runs after grouping, the only place the per-region average is known. Note the average is never shown; it only decides which regions make the cut, which is why MEA drops out even though its total profit looks close to the others.

Step 4: Sort by total profit, highest first

ORDER BY total_profit DESC puts the biggest earners on top. Adding region as a quiet secondary sort keeps equal totals in stable alphabetical order.

SELECT region,
       COUNT(order_id) AS order_count,
       SUM(profit)     AS total_profit
FROM orders
WHERE region IS NOT NULL
GROUP BY region
HAVING AVG(profit) > 740
;

Common follow-up questions

• If orders gained a `customer_id` and you genuinely needed unique customers per region, how would the query change? (Tests a real join plus COUNT(DISTINCT `customer_id`), the exact thing the decoy pretended to be.)
• A region averaging exactly 740 per order: in or out? (Boundary check on > versus >= at the 740 average.)
• If the ops team wanted regions filtered by total profit instead of average profit, which regions would change, and why is that a different query? (Tests that a HAVING on SUM(profit) selects a different set of regions than a HAVING on AVG(profit), and why.)