Previous City Most Common | Dataford Interview Questions

For customers that currently live in Seattle, what is the most common city they lived in previously? (ADDRESSES has customer_id, start_date, end_date, city...)

Asked in the technical phone screen stage. Tables include ORDERS, ITEMS, and ADDRESSES.

Output

Return one row with city and previous_customer_count.
Count distinct customers who previously lived in each non-NULL city.
Consider only customers whose current address is Seattle and whose previous address has a non-NULL end_date.
Return the most common city. Break ties alphabetically by city.

Schema

ADDRESSES

Column	Type	Description
address_idPK	INTEGER	Unique address history record identifier
customer_id	INTEGER	Customer associated with the address
start_date	DATE	Date the customer began living at the address
end_date	DATE	Date the customer stopped living at the address; NULL indicates the current address
city	VARCHAR(100)	City of the customer's address

Your question is Previous City Most Common. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem