Home / SQL / Practice problems / Medium

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
Show the expected result
customer_acustomer_bcity
Mia ChenBikash ThapaBrisbane
Mia ChenEthan NguyenBrisbane
Mia ChenKiran RaiBrisbane
Mia ChenNoah BrownBrisbane
Mia ChenSita GurungBrisbane
Liam WalkerAnjali KarkiSydney
Sita GurungBikash ThapaBrisbane
Sita GurungEthan NguyenBrisbane
Sita GurungKiran RaiBrisbane
Sita GurungNoah BrownBrisbane
Noah BrownBikash ThapaBrisbane
Noah BrownEthan NguyenBrisbane
Noah BrownKiran RaiBrisbane
Priya PatelChloe DaviesLondon
Priya PatelIsla TaylorLondon
Bikash ThapaEthan NguyenBrisbane
Bikash ThapaKiran RaiBrisbane
Ava WilsonJack MurphyKathmandu
Ava WilsonLeo RossiKathmandu
Ethan NguyenKiran RaiBrisbane
Isla TaylorChloe DaviesLondon
Lucas MartinGrace LeeMelbourne
Lucas MartinZoe ClarkeMelbourne
Zoe ClarkeGrace LeeMelbourne
Jack MurphyLeo RossiKathmandu

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.