Intermediate Joins · IN (subquery) · EXISTS
18. Customers with a big order
Concept: IN (subquery) · EXISTSshow
To filter rows by whether a match exists in another table, use WHERE id IN (SELECT ...) or WHERE EXISTS (SELECT 1 ... WHERE ...). Unlike a JOIN, these never duplicate rows: a customer with three matching orders still comes back once.
Example (different data):
SELECT name FROM authors WHERE id IN ( SELECT author_id FROM books WHERE rating >= 4.5 );
Your task
The account team is sending thank-you notes to every customer who has completed at least one order over $100. Each customer gets one note.
Return name and email of every customer with at least one completed order over 100, each customer once, sorted by name.
Solution unlocks after 3 more tries
Starting Postgres in your browser…
query.sql
Output
Write a query and press Run Query (Ctrl/⌘ + Enter).