D8LooPFocus modeCODE WORKSPACE
Customers with a shipped order
Return c.customer_id and c.country for customers who have at least one shipped order. Each customer should appear once. Order by customer_id.
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.buyer_id = c.customer_id AND o.status = 'shipped').
Sample input
customersAll 6 customers (customer_id, country shown).
| customer_id | country |
|---|---|
| 1 | US |
| 2 | null |
| 3 | GB |
| 4 | IN |
| 5 | US |
| 6 | GB |
ordersShipped orders belong to buyers 1, 3, and 4.
| order_id | buyer_id | status |
|---|---|---|
| 1004 | 1 | shipped |
| 1008 | 4 | shipped |
| 1012 | 3 | shipped |
Expected output
Expected outputCustomers with at least one shipped order, each once (a semi-join, no duplicates).
| customer_id | country |
|---|---|
| 1 | US |
| 3 | GB |
| 4 | IN |
Constraints
Use EXISTS with a correlated subquery on orders (a semi-join) so a customer with several shipped orders still appears once.
Expected skills
Semi-join with EXISTS for existence questions, returning each left row at most once and avoiding fan-out.
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.