Task
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'.
Schema
crm_customers
| column | type | description |
|---|
| customer_id | INT | Customer identifier |
| full_name | VARCHAR(100) | Customer name in CRM |
| email | VARCHAR(255) | Email stored in CRM |
| city | VARCHAR(100) | City stored in CRM |
| updated_at | TIMESTAMP | Last CRM profile update |
| | |
checkout_customers
| column | type | description |
|---|
| checkout_id | INT | Checkout profile row identifier |
| customer_id | INT | Customer identifier |
| email | VARCHAR(255) | Email captured in checkout |
| city | VARCHAR(100) | City captured in checkout |
| updated_at | TIMESTAMP | Last checkout profile update |
Sample data
Expected output
