Orders Without Shipments (Fulfillment Gap)
Which orders have been placed but do not yet have a shipment record?
- Joins
- NULL handling
- Filtering
- Sorting
Challenge brief
Understand the request
Fulfillment Operations has flagged a gap between placed orders and dispatched shipments and needs to identify which orders have no shipment record yet.
Find orders that have no corresponding shipment record — the fulfillment gap.
Return
- order_id
- customer_name (full name)
- order_date
- total_amount
- status
Constraints
- Include orders with no matching row in the shipments table
- Order by order_date ascending
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATETIMEtotal_amountREALstatusVARCHAR(50)
customers
customer_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)
shipments
shipment_idINTEGERorder_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
You need orders that have NO matching row in shipments. This is called an anti-join. The technique: LEFT JOIN orders to shipments, then filter WHERE the shipment side is NULL — meaning no match was found.
Hint 2
LEFT JOIN orders to shipments on order_id. Also INNER JOIN customers for the name. In the WHERE clause, add: WHERE s.shipment_id IS NULL. This keeps only orders with no shipment.
Hint 3
Scaffold: join order owners, preserve every order while matching shipments, keep the unmatched shipment keys, and sort chronologically.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT o.order_id, c.first_name || ' ' || c.last_name AS customer_name, o.order_date, o.total_amount, o.status FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id LEFT JOIN shipments s ON o.order_id = s.order_id WHERE s.shipment_id IS NULL ORDER BY o.order_date;Why this works
LEFT JOIN keeps every row from the left table (orders) even when no match exists on the right (shipments). Unmatched rows have NULL in all shipment columns. WHERE s.shipment_id IS NULL isolates exactly those unmatched orders — the anti-join pattern.
Success check
4 orders — including 2 still Processing and 2 marked Delivered with no shipment record
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_id | customer_name | order_date | total_amount | status |
|---|---|---|---|---|
| 5004 | Carol White | 2024-01-22 16:45:00 | 104.98 | Processing |
| 5007 | Emma Davis | 2024-02-03 10:20:00 | 39.99 | Processing |
| 5009 | Alice Johnson | 2024-02-10 14:00:00 | 89.99 | Delivered |
| 5010 | Bob Smith | 2024-02-15 11:30:00 | 124.99 | Delivered |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.