6. Orders with no payment
Asked in screens shaped like: PayPal, Capital One, Stripe
Every order should have a payment row. Find the ones that do not. This is the anti-join interviewers use to see whether you reach for NOT IN, which silently returns nothing when the subquery contains a NULL.
Requirements
- Columns: order_id, amount
- Return only orders with no matching payments row
- Order by order_id
- Do not use NOT IN
Expected output
order_id, amount
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
payments
~182 rows
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGER— joins orders
- methodTEXT— card, ach, wallet
- amountREAL
- paid_atTEXT
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
LEFT JOIN keeps every order, matched or not.
Hint 2
After the LEFT JOIN, unmatched rows have NULL on the payments side — filter on that.
Hint 3
LEFT JOIN payments p ON p.order_id = o.order_id WHERE p.order_id IS NULL
SELECT o.order_id, o.amount
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.order_id IS NULL
ORDER BY o.order_id;Why it works
LEFT JOIN plus IS NULL and NOT EXISTS both work and both stay correct with NULLs. NOT IN does not: if the subquery yields a single NULL, the predicate evaluates to UNKNOWN for every row and you get an empty result.
Run your query to see the result set.
Submit to run the checks.
Why it works
LEFT JOIN plus IS NULL and NOT EXISTS both work and both stay correct with NULLs. NOT IN does not: if the subquery yields a single NULL, the predicate evaluates to UNKNOWN for every row and you get an empty result.
Pro problem
Medium problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems