Selling Where Nobody Lives

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

An order with no destination on file belongs to no market, and only a shipped or completed order counts as a landed sale. From orders, list each region (region) that has landed at least one sale clearing more than $1,200 in profit.

Shipments land in regions our customer list has never heard of.

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 the question is really asking

Strip the marquee-sale story and this is a set-membership question: which destination regions contain at least one order that clears two bars at once, a fulfilled status ('Shipped' or 'Completed') and more than $1,200 of profit? The skill is applying both conditions to the SAME order row, then collapsing the survivors to one row per region. Miss that the two conditions must co-occur and 'EU' walks in, either on a $1,425 order that was still 'Pending' or on fulfilled orders worth a few hundred dollars each.

Why both conditions must land on the same row

A region earns its place only if a single order was both fulfilled and high-value. Filter profit on its own and you credit 'EU' for a $1,409 order that was 'Returned'. Filter status on its own and you credit regions whose fulfilled orders were small. Summing profit per region makes the same mistake from another direction, because it scores the region's total instead of any one order.

Profit filter alone (wrong)

SELECT DISTINCT region FROM orders WHERE profit > 1200 returns 'EU' off a 'Pending' order and a NULL region from a high-value order with no destination.

Both conditions on the same order (correct)

o.status IN ('Shipped', 'Completed'), o.region IS NOT NULL and o.order_id IN (SELECT order_id ...) all sit in one WHERE, so a region stays only when one order clears every bar.

Step 1: Keep only fulfilled orders

status IN ('Shipped', 'Completed') restricts the rows to orders that actually reached the customer. It is a two-value set, so a single equality loses half the fulfilled orders, and dropping it lets a 'Cancelled' or 'Returned' order qualify a region.

Step 2: Require the high-value test on the same order

o.order_id IN (SELECT order_id FROM orders WHERE profit > 1200) is a semi-join to the set of marquee orders. Because it matches on order_id, the profit bar and the fulfilled status must hold for the same order, not two different ones.

Step 3: Guard the missing region, then collapse

o.region IS NOT NULL drops orders with no destination on file, and DISTINCT reduces each surviving region to one row. The answer is a set of markets, so row order is not part of it.

SELECT DISTINCT o.region
FROM orders o
WHERE o.status IN ('Shipped', 'Completed')
  AND o.region IS NOT NULL
  AND o.order_id IN (
        SELECT order_id
        FROM orders
        WHERE profit > 1200
      )
;

Common follow-up questions

• How would the query change if 'high-value' were defined in a separate `flagged_orders` table instead of a profit threshold? (Tests whether they see the `IN` subquery as a reusable semi-join rather than a dressed-up profit predicate.)
• Rewrite the high-value condition as an `EXISTS` and as a `JOIN`, and explain which you would ship. (Checks fluency with the equivalent semi-join forms and their NULL and duplicate behavior.)
• How would you also report how many marquee orders each of these regions landed? (Extends the membership filter into a per-region count over the qualifying set.)