Your question is SQL Unified Profile With Latest Attributes. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
customer_id, external_id, first_name, last_name, email, phone, preferred_channel, marketing_opt_in, profile_updated_at, and preference_updated_at.customer_id ascending.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Stable customer identifier |
| external_id | VARCHAR(20) | Acxiom external customer identifier |
| 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 |
| VARCHAR(255) | Customer email address | |
| phone | VARCHAR(30) | Customer phone number |
| updated_at | TIMESTAMP | Profile version timestamp |
| 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 |