# Did We Actually Make Money? > Cancelled deals don't count. The rest shows where the money really came from. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Finance is reviewing order economics, and cancelled orders don't count since those deals never closed. Across the remaining orders, total the profit for each region and list the regions from the biggest earner down. ## Worked solution and explanation ### The finance question is a disguise This is a filtered group-total dressed up as a finance review. The skill being probed: can you sum profit per region over only the orders that survived a business filter, without letting that filter quietly distort which regions appear? Anyone can write a per-region sum. The real move is deciding where the 'cancelled orders don't count' rule lives, because that one choice decides whether a whole region can silently vanish from your report. ### The fork that separates candidates There are two honest ways to drop cancelled orders, and they do not return the same shape. Remove them in the WHERE clause and a region whose orders were all cancelled disappears from the result entirely. Zero them out inside a conditional sum and that region survives with a total of 0. Neither is wrong in the abstract. The interviewer wants to see that you noticed the difference and chose on purpose. **Filter in WHERE** WHERE status <> 'Cancelled' throws the cancelled rows away before grouping. A region with nothing but cancelled orders produces no group at all, so it drops off the report. That matches the ask here: we are reporting money actually earned, and a region that earned nothing has no line to show. **Conditional SUM** SUM(CASE WHEN status <> 'Cancelled' THEN profit ELSE 0 END) keeps every region, even ones where all orders were cancelled, showing them at 0. Use this when the report must list every region regardless of activity. It answers a different question than the one asked. Here the ask is for the biggest earners, so a region with zero surviving orders has nothing to earn and belongs off the list. The WHERE filter gives exactly that shape, and it is the leaner plan: rows are discarded before the aggregation instead of being carried through and zeroed. > **status <> 'Cancelled' quietly eats NULLs** > > In SQL three-valued logic, NULL <> 'Cancelled' evaluates to NULL, not true, so any order with a missing status is silently excluded by this filter. On a clean seed you never see it, but on real data one NULL status drops that order's profit from its region. If NULLs are possible, decide deliberately: keep them with status IS NULL OR status <> 'Cancelled', or exclude them on purpose. > **The customers table is a decoy** > > The schema hands you a customers table, but nothing in the question touches a customer, a name, or a country. Joining it in changes no total and only invites a fan-out bug. Recognizing that a provided table is irrelevant is itself a signal of seniority. #### Step 1: Drop the cancelled orders first Filter with WHERE status <> 'Cancelled' so those rows never reach the aggregation. This is the load-bearing line: forget it and APAC and MEA inherit their cancelled orders' profit, inflating every affected region. #### Step 2: Collapse to one row per region GROUP BY region, then SUM(profit) totals the surviving orders inside each region. This is where the two EU orders and the two US orders fold into single lines. #### Step 3: Round and order for the reader ROUND(SUM(profit), 2) keeps the money to cents, and ORDER BY total_profit DESC puts the biggest earner on top, which is what 'from the biggest earner down' asks for. **Per-region profit, cancelled orders excluded** ```sql SELECT region, ROUND(SUM(profit), 2) AS total_profit FROM orders WHERE status <> 'Cancelled' GROUP BY region ORDER BY total_profit DESC; ``` *One scan, one grouped aggregation, no join to the decoy table.* > **What the interviewer is watching for** > > The tell is whether you can articulate why cancelled orders leave via WHERE rather than a conditional sum, and whether you flag the NULL-status edge without being prompted. Candidates who name the row-drop-versus-zero distinction unasked read as senior; candidates who just paste a SUM and move on read as junior even when the number is right. > **Why this plan stays cheap** > > It is a single pass over orders: filter, then a hash or sort aggregate on region. No self-join, no subquery, no correlated lookup. At millions of orders this is bounded by one sequential scan plus a grouping on a low-cardinality key, so the aggregate footprint is tiny. A partial index on status, or a composite on (status, region, profit), lets the engine skip cancelled rows entirely. ## Common follow-up questions - Now include every region in the output, showing 0 for regions where all orders were cancelled. What changes? _(Forces the switch from a WHERE filter to a conditional SUM, testing whether they understood the fork.)_ - Some orders arrive with a NULL status. How does your query treat them today, and how would you handle them on purpose? _(Probes awareness of three-valued logic in the inequality filter.)_ - Finance also wants the count of orders behind each region's total. How would you add it in the same pass? _(Checks that they add COUNT(*) alongside SUM without a second scan.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/did_we_actually_make_money) - [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.