Customers who share a city
Medium
Show pairs of customers in the same city: customer_a, customer_b, city, each pair once.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Your query✔ Solved
Show the expected result
| customer_a | customer_b | city |
|---|---|---|
| Mia Chen | Bikash Thapa | Brisbane |
| Mia Chen | Ethan Nguyen | Brisbane |
| Mia Chen | Kiran Rai | Brisbane |
| Mia Chen | Noah Brown | Brisbane |
| Mia Chen | Sita Gurung | Brisbane |
| Liam Walker | Anjali Karki | Sydney |
| Sita Gurung | Bikash Thapa | Brisbane |
| Sita Gurung | Ethan Nguyen | Brisbane |
| Sita Gurung | Kiran Rai | Brisbane |
| Sita Gurung | Noah Brown | Brisbane |
| Noah Brown | Bikash Thapa | Brisbane |
| Noah Brown | Ethan Nguyen | Brisbane |
| Noah Brown | Kiran Rai | Brisbane |
| Priya Patel | Chloe Davies | London |
| Priya Patel | Isla Taylor | London |
| Bikash Thapa | Ethan Nguyen | Brisbane |
| Bikash Thapa | Kiran Rai | Brisbane |
| Ava Wilson | Jack Murphy | Kathmandu |
| Ava Wilson | Leo Rossi | Kathmandu |
| Ethan Nguyen | Kiran Rai | Brisbane |
| Isla Taylor | Chloe Davies | London |
| Lucas Martin | Grace Lee | Melbourne |
| Lucas Martin | Zoe Clarke | Melbourne |
| Zoe Clarke | Grace Lee | Melbourne |
| Jack Murphy | Leo Rossi | Kathmandu |
25 rows
Show a solution
SELECT a.name AS customer_a, b.name AS customer_b, a.city FROM customers a JOIN customers b ON a.city = b.city AND a.customer_id < b.customer_id;
SQL used
New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.