Normalize Customer Addresses by Region

Task

You are given raw customer addresses exported from a TELUS Digital support workflow. The data contains inconsistent province and country values such as mixed casing, abbreviations, punctuation, and missing region mappings. Write a PostgreSQL query that returns one row per address with a normalized province code and normalized country name, then classifies each record as normalized, needs_review, or invalid.

Use the lookup tables to match cleaned province and country values. If a province cannot be matched but the country is Canada or the United States, mark the row as needs_review. If the country itself cannot be matched, mark the row as invalid. Otherwise mark the row as normalized.

Schema

Table	Column	Type	Description
customer_addresses	address_id	INT	Address record ID
customer_addresses	customer_name	VARCHAR(100)	Customer name
customer_addresses	street_address	VARCHAR(150)	Raw street address
customer_addresses	city	VARCHAR(100)	City name
customer_addresses	province_raw	VARCHAR(50)	Raw province/state value
customer_addresses	country_raw	VARCHAR(50)	Raw country value
province_reference	province_code	VARCHAR(10)	Standard province/state code
province_reference	province_name	VARCHAR(100)	Standard province/state name
province_reference	country_code	VARCHAR(10)	Country code for province/state
country_reference	country_code	VARCHAR(10)	Standard country code
country_reference	country_name	VARCHAR(100)	Standard country name
country_reference	country_alias	VARCHAR(100)	Acceptable raw country alias

Sample data

address_id	customer_name	city	province_raw	country_raw
1	Ava Chen	Toronto	on	canada
2	Liam Patel	Vancouver	British Columbia	CA
4	Noah Smith	Seattle	wa.	usa
7	Sophia Brown	Montreal		Canada
10	Mia Wilson	Calgary	Alberta	CAN

Expected output

address_id	customer_name	normalized_province_code	normalized_country_name	address_status
1	Ava Chen	ON	Canada	normalized
7	Sophia Brown	NULL	Canada	needs_review
8	James Lee	NULL	NULL	invalid

Problem

Task

Schema

Table	Column	Type	Description
customer_addresses	address_id	INT	Address record ID
customer_addresses	customer_name	VARCHAR(100)	Customer name
customer_addresses	street_address	VARCHAR(150)	Raw street address
customer_addresses	city	VARCHAR(100)	City name
customer_addresses	province_raw	VARCHAR(50)	Raw province/state value
customer_addresses	country_raw	VARCHAR(50)	Raw country value
province_reference	province_code	VARCHAR(10)	Standard province/state code
province_reference	province_name	VARCHAR(100)	Standard province/state name
province_reference	country_code	VARCHAR(10)	Country code for province/state
country_reference	country_code	VARCHAR(10)	Standard country code
country_reference	country_name	VARCHAR(100)	Standard country name
country_reference	country_alias	VARCHAR(100)	Acceptable raw country alias

Sample data

address_id	customer_name	city	province_raw	country_raw
1	Ava Chen	Toronto	on	canada
2	Liam Patel	Vancouver	British Columbia	CA
4	Noah Smith	Seattle	wa.	usa
7	Sophia Brown	Montreal		Canada
10	Mia Wilson	Calgary	Alberta	CAN

Expected output

address_id	customer_name	normalized_province_code	normalized_country_name	address_status
1	Ava Chen	ON	Canada	normalized
7	Sophia Brown	NULL	Canada	needs_review
8	James Lee	NULL	NULL	invalid

Problem

Task

Schema

Table	Column	Type	Description
customer_addresses	address_id	INT	Address record ID
customer_addresses	customer_name	VARCHAR(100)	Customer name
customer_addresses	street_address	VARCHAR(150)	Raw street address
customer_addresses	city	VARCHAR(100)	City name
customer_addresses	province_raw	VARCHAR(50)	Raw province/state value
customer_addresses	country_raw	VARCHAR(50)	Raw country value
province_reference	province_code	VARCHAR(10)	Standard province/state code
province_reference	province_name	VARCHAR(100)	Standard province/state name
province_reference	country_code	VARCHAR(10)	Country code for province/state
country_reference	country_code	VARCHAR(10)	Standard country code
country_reference	country_name	VARCHAR(100)	Standard country name
country_reference	country_alias	VARCHAR(100)	Acceptable raw country alias

Sample data

address_id	customer_name	city	province_raw	country_raw
1	Ava Chen	Toronto	on	canada
2	Liam Patel	Vancouver	British Columbia	CA
4	Noah Smith	Seattle	wa.	usa
7	Sophia Brown	Montreal		Canada
10	Mia Wilson	Calgary	Alberta	CAN

Expected output

address_id	customer_name	normalized_province_code	normalized_country_name	address_status
1	Ava Chen	ON	Canada	normalized
7	Sophia Brown	NULL	Canada	needs_review
8	James Lee	NULL	NULL	invalid

Problem

Task

Schema

Table	Column	Type	Description
customer_addresses	address_id	INT	Address record ID
customer_addresses	customer_name	VARCHAR(100)	Customer name
customer_addresses	street_address	VARCHAR(150)	Raw street address
customer_addresses	city	VARCHAR(100)	City name
customer_addresses	province_raw	VARCHAR(50)	Raw province/state value
customer_addresses	country_raw	VARCHAR(50)	Raw country value
province_reference	province_code	VARCHAR(10)	Standard province/state code
province_reference	province_name	VARCHAR(100)	Standard province/state name
province_reference	country_code	VARCHAR(10)	Country code for province/state
country_reference	country_code	VARCHAR(10)	Standard country code
country_reference	country_name	VARCHAR(100)	Standard country name
country_reference	country_alias	VARCHAR(100)	Acceptable raw country alias

Sample data

address_id	customer_name	city	province_raw	country_raw
1	Ava Chen	Toronto	on	canada
2	Liam Patel	Vancouver	British Columbia	CA
4	Noah Smith	Seattle	wa.	usa
7	Sophia Brown	Montreal		Canada
10	Mia Wilson	Calgary	Alberta	CAN

Expected output

address_id	customer_name	normalized_province_code	normalized_country_name	address_status
1	Ava Chen	ON	Canada	normalized
7	Sophia Brown	NULL	Canada	needs_review
8	James Lee	NULL	NULL	invalid

Interview Guides

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output

Normalize Customer Addresses by Region

Problem

Task

Schema

Sample data

Expected output

Problem

Task

Schema

Sample data

Expected output