Employee Address Lookup - Multi-table SQL Join and Data Cleaning
Interview Experience
Problem
You have three tables:
sql
employees(id INT, name VARCHAR, department_id INT)
addresses(employee_id INT, street VARCHAR, city VARCHAR, country VARCHAR, is_primary BOOLEAN)
departments(id INT, name VARCHAR, location VARCHAR)
Write SQL queries for:
Q1:
Return each employee's name, their primary address city, and their department name. Include employees with no address on file (show NULL for city).
Q2: Find all employees whose primary address country differs from their department's location country.
Q3: Some employees have multiple rows marked is_primary = TRUE (data error).
Return a list of those employee IDs and the count of duplicate primary addresses.
Example output for Q3:
employee_id | duplicate_count
------------+----------------
1042 | 2
2871 | 3
Follow-ups
- How would you fix the duplicate primary addresses in a single UPDATE/DELETE statement?
- What index would you add to make Q2 performant on a 5M-row addresses table?
- Rewrite Q1 using a CTE for readability.
- How would you handle employees with addresses in multiple countries?
Full Details
Problem
You have three tables:
sql
employees(id INT, name VARCHAR, department_id INT)
addresses(employee_id INT, street VARCHAR, city VARCHAR, country VARCHAR, is_primary BOOLEAN)
departments(id INT, name VARCHAR, location VARCHAR)
Write SQL queries for:
Q1:
Return each employee's name, their primary address city, and their department name. Include employees with no address on file (show NULL for city).
Q2: Find all employees whose primary address country differs from their department's location country.
Q3: Some employees have multiple rows marked is_primary = TRUE (data error).
Return a list of those employee IDs and the count of duplicate primary addresses.
Example output for Q3:
employee_id | duplicate_count
------------+----------------
1042 | 2
2871 | 3
Follow-ups
- How would you fix the duplicate primary addresses in a single UPDATE/DELETE statement?
- What index would you add to make Q2 performant on a 5M-row addresses table?
- Rewrite Q1 using a CTE for readability.
- How would you handle employees with addresses in multiple countries?