Your question is Resolve Conflicting Customer Attributes. Start with the requirements and the two 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.
You are given customer profile data from ACME House CRM and ACME House Checkout, and some fields conflict across sources. Write a PostgreSQL query that returns one resolved row per customer for customers who appear in both systems. For each customer, choose the most trustworthy email and city using this logic: prefer the value from the most recently updated record; if the latest value is NULL or an empty string, fall back to the other source. Also return a discrepancy_flag of 'conflict' when the non-blank values differ across the two sources, otherwise 'match_or_missing'.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Customer identifier in CRM |
| full_name | VARCHAR(100) | Customer full name |
| VARCHAR(255) | Email stored in CRM | |
| city | VARCHAR(100) | City stored in CRM |
| updated_at | TIMESTAMP | Last CRM update timestamp |
| Column | Type | Description |
|---|---|---|
| checkout_idPK | INT | Checkout profile row identifier |
| customer_id | INT | Customer identifier linked to CRM |
| VARCHAR(255) | Email captured in checkout | |
| city | VARCHAR(100) | City captured in checkout |
| updated_at | TIMESTAMP | Last checkout update timestamp |