Normalize Customer Addresses by Region

HardSQL · PostgreSQL

Problem

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

customer_addresses

Column	Type	Description
address_idPK	INT	Address record ID
customer_name	VARCHAR(100)	Customer name
street_address	VARCHAR(150)	Raw street address
city	VARCHAR(100)	City name
province_raw	VARCHAR(50)	Raw province or state value
country_raw	VARCHAR(50)	Raw country value

province_reference

Column	Type	Description
province_codePK	VARCHAR(10)	Standard province or state code
province_name	VARCHAR(100)	Standard province or state name
country_code	VARCHAR(10)	Country code for the province or state

country_reference

Column	Type	Description
country_code	VARCHAR(10)	Standard country code
country_name	VARCHAR(100)	Standard country name
country_aliasPK	VARCHAR(100)	Accepted raw alias for the country

Tablescustomer_addressesprovince_referencecountry_reference