Did We Actually Make Money?

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

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 rank the regions from the most profitable down to the least.

Cancelled deals don't count. The rest shows where the money really came from.

Tables

orders

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

customers

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

Expected output

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.

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.

SELECT
    region,
    ROUND(SUM(profit), 2) AS total_profit
FROM orders
WHERE status <> 'Cancelled'
GROUP BY region
ORDER BY total_profit DESC;

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.)