Drill 8 of 12 in Interview Classics
0

Interview Classics · EXISTS · NOT EXISTS · per-customer anti-join

8. Bought oat milk, never coffee beans

Concept: EXISTS · NOT EXISTS · per-customer anti-joinshow

'Did A but never B' depends on a customer's whole history, so a WHERE on single rows can't answer it: item = 'a' AND item <> 'b' is true for every 'a' row. Check the history with EXISTS (...) for A and NOT EXISTS (...) for B, both correlated on the customer. EXCEPT between two lists of ids works too.

Example (different data):

SELECT s.name
FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM enrolments e
  WHERE e.student_id = s.id AND e.course = 'Calculus'
);

Your task

A coffee roaster partnering with the grocery app wants to send a free sample to shoppers who buy oat milk but have never bought coffee beans.

Return the username of every shopper who has bought oat milk at least once and has never bought coffee beans in any basket, each shopper once, sorted by username.

Starting Postgres in your browser…

Checking your access…