Using a self-join, return every pair of customers who live in the same city: cust1 (name), cust2 (name), city. Each…
SQLLevel: IntermediateBonus drills (mixed difficulty)
Using a self-join, return every pair of customers who live in the same city: cust1 (name), cust2 (name), city. Each pair should appear once (no self-pairs, no reversed duplicates).
Table definitions (from the source exercise):
CREATE TABLE customers(customer_id INTEGER PRIMARY KEY, name TEXT, city TEXT, signup_date TEXT, credit_score INTEGER);
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
Approach: Join customers to itself on city, and require a.customer_id < b.customer_id to kill self-pairs and mirror duplicates.
SELECT a.name AS cust1, b.name AS cust2, a.city
FROM customers a
JOIN customers b ON b.city = a.city
AND a.customer_id < b.customer_id;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT