Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
The in-store team want the opposite of the combined-offer list: customer-months that happened online only, so they can try to pull those customers into a shop.
Return the customer_id and month pairs present in shop_orders but not in store_orders, ordered by customer_id then month.
Result columns · in this order
customer_id | Customer who ordered online in this month. |
month | The month, as YYYY-MM, with no in-store sale for that customer. |
How to approach it
Same two projections as the INTERSECT question; EXCEPT keeps what is in the first branch only.
Sample input
| order_id | customer_id | placed_at | status | order_total |
|---|---|---|---|---|
| 1001 | c1 | 2026-01-05 10:00:00 | paid | 120 |
| 1002 | c2 | 2026-01-12 14:30:00 | Paid | 80 |
| 1003 | c1 | 2026-01-20 09:15:00 | pending | 45 |
| 1004 | c3 | 2026-01-31 23:30:00 | PAID | 200 |
| 1005 | c2 | 2026-02-01 00:15:00 | paid | 60 |
| 1006 | c4 | 2026-02-03 11:00:00 | paid | 150 |
| 1007 | c3 | 2026-02-10 16:45:00 | refunded | 90 |
| 1008 | c1 | 2026-02-14 12:00:00 | paid | 300 |
| 1009 | c5 | 2026-02-18 08:30:00 | Paid | 75 |
| 1010 | c4 | 2026-02-25 19:20:00 | pending | 85 |
| 1011 | c5 | 2026-02-28 21:00:00 | paid | 130 |
| 1012 | c2 | 2026-03-02 10:10:00 | paid | 95 |
12 rows — scroll inside the table to see them all.
| order_id | customer_id | sold_on | status | order_total |
|---|---|---|---|---|
| 2001 | c1 | 2026-01-05 | paid | 120 |
| 2002 | c3 | 2026-02-05 | paid | 40 |
| 2003 | c5 | 2026-02-18 | paid | 75 |
| 2004 | c2 | 2026-03-01 | pending | 55 |
| 2005 | c4 | 2026-03-04 | paid | 65 |
| 2006 | c1 | 2026-03-06 | paid | 20 |
| 2007 | c3 | 2026-03-10 | paid | 90 |
| 2008 | c2 | 2026-03-12 | paid | 80 |
8 rows — all rows shown.
Expected output
| customer_id | month |
|---|---|
| c1 | 2026-02 |
| c2 | 2026-01 |
| c2 | 2026-02 |
| c3 | 2026-01 |
| c4 | 2026-02 |
5 rows — all rows shown.
Constraints
EXCEPT. Direction matters: A EXCEPT B is what is in A and not in B, and swapping the branches answers a different question.(customer_id, month) in the same shape and the same order — set operators compare by position.EXCEPT deduplicates, so a customer with four online orders in one uncovered month appears once.EXCEPT does with NULLs before you rely on it: it treats two NULLs as the same value, unlike =. Neither column here is nullable, but the behaviour is the reason NOT IN and EXCEPT disagree on other tables.customer_id, then month.Worked example
c2 ordered online in 2026-01 and there is no in-store sale to c2 that month, so (c2, 2026-01) is in the result. c2 also ordered online in 2026-03 — and bought in store that month — so that pair is excluded.
Five pairs qualify. The NOT EXISTS form returns the same rows and needs a correlated subquery matching on both columns; the NOT IN form is the one to avoid, because a NULL anywhere in the subquery result makes the whole thing return nothing.
The set operator is the shortest correct answer here precisely because the comparison really is over whole rows.
What this tests
Whether you can express an anti-join as a set difference, and whether you keep the direction of EXCEPT straight.
Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.