# Selling Where Nobody Lives > Shipments land in regions our customer list has never heard of. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Our revenue team is mapping which markets have actually landed a marquee sale. Return each destination region (`region`) that has a shipped or completed order clearing more than $1,200 in profit, listed alphabetically. ## Worked solution and explanation ### What the question is really asking Strip the marquee-sale story and this is a set-membership question: of the destination regions, which ones contain at least one order that clears two bars at the same time, a fulfilled status (shipped or completed) and more than $1,200 in profit? The skill is applying both conditions to the SAME order, then collapsing the survivors to a distinct, alphabetically ordered set of regions. Miss that the two conditions must co-occur and EU walks in on a $1,425 order that was still Pending, or on a fulfilled order worth $50. ### 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 big order that was Returned; filter status on its own and you credit regions whose fulfilled orders were tiny. The correct read is: does this region have at least one order that is simultaneously in a fulfilled status and above the profit bar? That co-occurrence, on one order row, is the whole problem. > **Two silent wrong answers** > > Two mistakes both pass a quick eyeball and neither errors out. Selecting region without DISTINCT returns the same region once per qualifying order, and skipping region IS NOT NULL emits a blank row from a high-value fulfilled order that has no destination recorded. Both are simply wrong answers that look plausible. **Profit filter alone (wrong)** SELECT DISTINCT region FROM orders WHERE profit > 1200 ORDER BY region; wrongly returns EU (it has a $1,425 Pending order) and can emit a blank region from a high-value order with no destination on file. **Both conditions on the same order (correct)** 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) ORDER BY o.region; keeps a region only when one order clears both bars. #### Step 1: Keep only fulfilled orders status IN ('Shipped', 'Completed') restricts to orders that actually reached the customer. Drop it and a region can qualify on a Cancelled or Returned order, which is not a landed sale. #### Step 2: Require a high-value order in the same row 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 both hold for the SAME order, not two different ones. #### Step 3: Guard the blank region, then collapse and sort region IS NOT NULL drops orders with no destination on file, DISTINCT reduces each surviving region to one row, and ORDER BY region gives the stable alphabetical list the team scans top to bottom. **Regions that landed a high-value fulfilled order** ```sql 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 ) ORDER BY o.region; ``` *The IN subquery is a semi-join to the high-value orders; matching on order_id forces the profit and status conditions onto the same row.* > **The tell they are listening for** > > Saying out loud 'both the status and the profit have to be true on the same order' separates candidates. The ones who filter profit alone, or forget DISTINCT and the null guard, quietly report EU and a blank row as marquee markets and never notice. > **In production, reach for a semi-join** > > Here the high-value set lives in the same table, so an IN subquery on order_id and a plain profit predicate happen to be equivalent. The subquery form is the one that generalizes: when 'high-value' is defined in a separate flagged-orders table or by a richer rule, the same shape reads cleanly as WHERE EXISTS (SELECT 1 FROM ...). EXISTS is NULL-safe and lets the optimizer stop at the first matching order, so it stays cheap as the table grows. ## 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 that generalizes across tables, not just 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 three equivalent semi-join forms and their NULL behavior.)_ - How would you also report how many marquee orders each of these regions landed? _(Extends the membership filter into an aggregation over the qualifying set.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/selling_where_nobody_lives) - [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.