# Lines on the Map > Every region keeps its own ledger. The totals set the order. Canonical URL: 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, highest total profit first. ## 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 tell is a missing key** > > Scan both tables for a shared column. Orders has order_id, status, region, profit. Customers has customer_id, first_name, last_name, country. Nothing connects an order to a customer. When there is no join key, the honest answer uses one table, and here that table is orders. ### 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. > **Common pitfall** > > The expected output preview has three columns and not one shred of customer information. If your draft has a JOIN in it, that is the signal you were baited by the extra table rather than reading what the output actually asks for. ### 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. **Regional recap** ```sql 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 ORDER BY total_profit DESC, region; ``` *One table, one grouping. The average gates the rows; the customers table is never touched.* > **Interviewers watch for** > > Seniors say 'there is no key to join on' out loud, then notice the filter is on an average and reach for HAVING without hesitating. Juniors write the join because two tables were handed to them, and try to filter the average in WHERE. Naming the decoy and placing the aggregate filter correctly is the tell that separates them. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/lines_on_the_map) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.