Keep Every Person When Attaching Address Details
Company: Google
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Other
### Problem
Report firstName, lastName, city and state for every Person. Match addresses by personId; where no address matches, city and state must be NULL. Row order is unrestricted. A person with several matching addresses contributes one row per address, even if some rows have the same displayed values.
### Tables
Table: `Person`
```text
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| personId | int |
| lastName | varchar |
| firstName | varchar |
+-------------+---------+
personId is the primary key (column with unique values) for this table.
This table contains information about the ID of some persons and their first and last names.
```
Table: `Address`
```text
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| addressId | int |
| personId | int |
| city | varchar |
| state | varchar |
+-------------+---------+
addressId is the primary key (column with unique values) for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.
```
### Example
```text
Person (personId,lastName,firstName): (4,Lee,Ada), (7,Chen,Bo)
Address (addressId,personId,city,state): (9,7,Portland,Oregon), (13,7,Portland,Oregon)
Output (firstName,lastName,city,state): (Ada,Lee,NULL,NULL), (Bo,Chen,Portland,Oregon), (Bo,Chen,Portland,Oregon)
```
### What a Strong Answer Covers
- Return every person and every matching address row.
- Keep repeated output rows when different matching address records have equal displayed values.
### Follow-up Questions
- How would you report only one address per person if the table recorded an update time?
- How would you include people who have no address in a per-state count?
Overview: Attach every matching address to each person and keep people without any address with NULL city and state.