00:00

MediumAbout 8 minEXISTS & Anti-JoinsLesson: EXISTS & Anti-Joins (P08) →

Customers Who Bought Product p_001

The marketing team wants to identify registered customers who have ordered a particular product.

The customers table lists registered customers, orders records their orders, and order_items identifies the products in each order.

Prepare a report listing customers who have ordered product p_001 at least once, regardless of the order's status. A customer should appear only once, even if they ordered the product several times.

Ordering: Sort by customer_id in ascending order.

Returncustomer_id

Columns

customers6 columns

columntype

customer_idVARCHAR

signup_tsTIMESTAMP WITH TIME ZONE

countryVARCHAR

segmentVARCHAR

phoneVARCHAR

full_nameVARCHAR

orders5 columns

columntype

order_idVARCHAR

customer_idVARCHAR

order_tsTIMESTAMP WITH TIME ZONE

statusVARCHAR

amount_usdBIGINT

order_items4 columns

columntype

order_idVARCHAR

product_idVARCHAR

qtyBIGINT

unit_priceBIGINT

Sample tables

customers4 rows

customer_idsignup_tscountrysegmentphonefull_name

c_0012026-05-01 09:00:00INpremium+91-9876543210Ananya Sharma

c_0022026-05-03 11:30:00USpremium+1-415-555-0123Brad Miller

c_0032026-05-05 14:15:00INfreeNULLChen Wei

c_0042026-05-10 08:45:00UKfree+44-20-7946-0958Dhruv Patel

orders5 rows

order_idcustomer_idorder_tsstatusamount_usd

o_002c_0012026-05-15 11:00:00paid200

o_004c_0022026-05-12 14:20:00paid150

o_006c_0042026-05-18 16:40:00paid300

o_007c_0042026-05-22 11:50:00paid100

o_009c_0022026-05-26 09:00:00delivered200

order_items6 rows

order_idproduct_idqtyunit_price

o_002p_0011200

o_004p_0021150

o_006p_0011200

o_006p_0021100

o_007p_0011100

o_009p_0011200

Expected output

3 rows

customer_id

c_001

c_002

c_004

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise answering a yes-or-no question across three tables: has this customer ever ordered product p_001? The path runs from customers through orders to order_items, and the report needs one row per qualifying customer, not one row per matching order line. It’s the kind of list a marketing team uses to target or exclude past buyers of a product, so a customer listed twice would be counted twice in any follow-up. You’ll decide how to test for the existence of a match rather than collect every match, and keep every order status in play.

The usual mistake is to chain two INNER JOINs from customers to order_items, filter on product_id = 'p_001' and return customer_id. That looks right, but a join returns one row for every matching line, so a customer who bought p_001 in two separate orders appears twice. In the sample, one customer has p_001 on more than one order, which is exactly where the duplicate shows up. Tacking on DISTINCT hides the symptom but still builds every matching row first, when the question is only whether at least one such line exists. The second trap is filtering on orders.status = 'paid' out of habit. The prompt says the order’s status doesn’t matter, and the sample includes a p_001 order that is delivered rather than paid, so a status filter silently removes a genuine buyer.

Read the lesson: EXISTS & Anti-Joins (P08) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows