SQL Unified Profile With Latest Attributes

MediumSQL · PostgreSQL

Problem

Write a SQL query to combine customer records from multiple tables and return a unified profile with the latest attributes at Acxiom.

Use the provided customer master, profile history, and preference history tables. Return every customer, including customers without matching history records.

Output

One row per customer with customer_id, external_id, first_name, last_name, email, phone, preferred_channel, marketing_opt_in, profile_updated_at, and preference_updated_at.
Use the latest profile and preference record for each customer; retain NULLs when no matching record exists.
Order by customer_id ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Stable customer identifier
external_id	VARCHAR(20)	Acxiom external customer identifier

customer_profile_history

Column	Type	Description
profile_idPK	INT	Profile history record identifier
customer_id	INT	Referenced customer
first_name	VARCHAR(80)	Customer first name
last_name	VARCHAR(80)	Customer last name
email	VARCHAR(255)	Customer email address
phone	VARCHAR(30)	Customer phone number
updated_at	TIMESTAMP	Profile version timestamp

customer_preference_history

Column	Type	Description
preference_idPK	INT	Preference history record identifier
customer_id	INT	Referenced customer
preferred_channel	VARCHAR(20)	Preferred communication channel
marketing_opt_in	BOOLEAN	Marketing consent status
updated_at	TIMESTAMP	Preference version timestamp

Tablescustomerscustomer_profile_historycustomer_preference_history