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
Marketing want to know which customers shop in both channels in the same month — those are the ones worth a combined offer, rather than customers who happen to appear in both tables at some point across the year.
Return the customer_id and month pairs that appear in both shop_orders and store_orders, ordered by customer_id then month.
Result columns · in this order
customer_id | Customer present in both channels in the same month. |
month | The month, as YYYY-MM, in which both channels saw them. |
How to approach it
Project (customer_id, month) from each table in the same shape, then INTERSECT them.
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-01 |
| c2 | 2026-03 |
| c3 | 2026-02 |
| c5 | 2026-02 |
4 rows — all rows shown.
Constraints
c1 buying online in January and in store in March is not a match.INTERSECT. It compares whole rows, which is exactly the semantics the requirement describes, and it deduplicates as part of its definition.placed_at on the online side, sold_on in store — so the branches will not look symmetrical. Set operators match by position, never by name.YYYY-MM), or nothing matches and the query returns zero rows with no error.customer_id, then month.Worked example
c1 has an online order in 2026-01 and an in-store sale in 2026-01, so (c1, 2026-01) is in the result. c1 also has online orders in 2026-02 with no in-store sale that month, so (c1, 2026-02) is not.
Four pairs qualify. Written as a join instead, this needs DISTINCT — a customer with two online orders and one in-store sale in the same month produces two joined rows, and the count of "customers in both channels" quietly doubles.
That is the argument for the set operator: INTERSECT is defined over rows and deduplicates by definition, so there is no fan-out to remember to clean up.
What this tests
Whether you reach for a set operator when the question is genuinely about sets, and whether you noticed the join version needs a DISTINCT the set operator does not.
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.