Problem

Given a table of customer data, write a SQL query to find the names of all customers who live in the same city as the customer with the name 'John Smith'.
Example input:
| customer_id | name       | city      |
|--------------|------------|-----------|
| 1            | John Smith | New York  |
| 2            | Jane Doe   | Chicago   |
| 3            | Joe Schmo  | New York  |
Example output:
| customer_id | name       |
|--------------|------------|
| 1            | John Smith |
| 3            | Joe Schmo  |

Solution

SELECT name
FROM customers
WHERE city = (SELECT city FROM customers WHERE name = 'John Smith')
AND name != 'John Smith';

/*

The solution above is optimal because it uses a subquery to find the city of John Smith, and then uses that city to find all customers who live in that city.

*/