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'.
crm_customers| column | type | description |
|---|---|---|
| customer_id | INT | Customer identifier |
| full_name | VARCHAR(100) | Customer name in CRM |
| 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 |
| VARCHAR(255) | Email captured in checkout | |
| city | VARCHAR(100) | City captured in checkout |
| updated_at | TIMESTAMP | Last checkout profile update |
| customer_id | crm_email | checkout_email | crm_city | checkout_city |
|---|---|---|---|---|
| 101 | ava.chen@oldmail.com | ava.chen@acmehouse.com | Seattle | Seattle |
| 102 | ben.lee@example.com | Portland | Portland | |
| 103 | cara.ng@example.com | cara.ng@example.com | Denver | Boulder |
| customer_id | resolved_email | resolved_city | discrepancy_flag |
|---|---|---|---|
| 101 | ava.chen@acmehouse.com | Seattle | conflict |
| 102 | ben.lee@example.com | Portland | match_or_missing |
| 103 | cara.ng@example.com | Boulder | conflict |
| 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 |
| customer_id | full_name | city | updated_at | |
|---|---|---|---|---|
| 101 | Ava Chen | ava.chen@oldmail.com | Seattle | 2024-02-10 09:00:00 |
| 102 | Ben Lee | Portland | 2024-02-12 10:30:00 | |
| 103 | Cara Ng | cara.ng@example.com | Denver | 2024-02-08 08:15:00 |
| 104 | Diego Ruiz | diego.ruiz@example.com | 2024-02-11 14:00:00 | |
| 105 | Ella Park | ella.park@example.com | San Jose | 2024-02-09 16:45:00 |
| 106 | Farah Ali | Austin | 2024-02-07 11:20:00 | |
| 107 | Gabe Kim | gabe.kim@example.com | Boston | 2024-02-13 12:00:00 |
| 108 | Hana Singh | hana.singh@example.com | 2024-02-06 09:40:00 | |
| 109 | Ivan Torres | ivan.torres@example.com | Chicago | 2024-02-05 13:10:00 |
| 110 | Ava Chen | gabe.kim@example.com | Austin | 2024-02-08 10:13:40 |
| 111 | Victor Chen | quinn.foster@example.com | Los Angeles | 2024-02-15 04:51:22 |
| 112 | Quinn Foster | olivia.reed@example.com | Chicago | 2024-02-14 06:34:36 |
| 113 | Cara Ng | liam.brooks@example.com | Sacramento | 2024-02-04 12:53:31 |
| 114 | Ben Lee | ximena.cruz@example.com | Atlanta | 2024-02-13 10:36:59 |
| 115 | Gabe Kim | theo.bennett@example.com | Cambridge | 2024-02-07 13:48:13 |
| 116 | Victor Chen | ava.chen@oldmail.com | Houston | 2024-02-04 07:37:05 |
| 117 | Liam Brooks | ximena.cruz@example.com | San Diego | 2024-02-14 15:13:09 |
| 118 | Maya Lopez | yara.hassan@example.com | Nashville | 2024-02-10 18:28:28 |
| 119 | Olivia Reed | hana.singh@example.com | Seattle | 2024-02-12 18:57:26 |
| 120 | Ximena Cruz | kira.patel@example.com | null | 2024-02-11 01:45:43 |
| 121 | Gabe Kim | yara.hassan@example.com | San Diego | 2024-02-16 04:27:11 |
| 122 | Ivan Torres | quinn.foster@example.com | Atlanta | 2024-02-15 20:31:55 |
| 123 | Farah Ali | will.harper@example.com | Salt Lake City | 2024-02-03 17:08:41 |
| 124 | Ben Lee | ella.park@example.com | Cambridge | 2024-02-05 07:15:55 |
| 125 | Farah Ali | maya.lopez@example.com | null | 2024-02-05 14:02:23 |
| 126 | Gabe Kim | ximena.cruz@example.com | Minneapolis | 2024-02-13 06:39:02 |
| 127 | Ivan Torres | will.harper@example.com | null | 2024-02-13 16:49:54 |
| 128 | Zane Cooper | olivia.reed@example.com | Austin | 2024-02-10 07:54:53 |
| checkout_id | customer_id | city | updated_at | |
|---|---|---|---|---|
| 201 | 101 | ava.chen@acmehouse.com | Seattle | 2024-02-15 08:00:00 |
| 202 | 102 | ben.lee@example.com | Portland | 2024-02-10 09:00:00 |
| 203 | 103 | cara.ng@example.com | Boulder | 2024-02-14 17:00:00 |
| 204 | 104 | Phoenix | 2024-02-12 15:00:00 | |
| 205 | 105 | ella.park@example.com | San Jose | 2024-02-08 10:00:00 |
| 206 | 106 | farah.ali@example.com | 2024-02-09 18:30:00 | |
| 207 | 107 | gabe.kim@example.com | Cambridge | 2024-02-10 09:45:00 |
| 208 | 108 | Dallas | 2024-02-16 07:20:00 | |
| 209 | 110 | jules.martin@example.com | Miami | 2024-02-11 11:00:00 |
| 210 | 103 | cara.ng@example.com | Denver | 2024-02-01 12:00:00 |
| 211 | 106 | null | Houston | 2024-01-31 00:37:35 |
| 212 | 103 | rafael.gomez@example.com | Atlanta | 2024-02-12 18:54:23 |
| 213 | 103 | will.harper@example.com | Atlanta | 2024-02-06 03:55:33 |
| 214 | 103 | null | null | 2024-02-17 16:24:40 |
| 215 | 110 | priya.shah@example.com | Chicago | 2024-02-05 14:28:22 |
| 216 | 104 | rafael.gomez@example.com | null | 2024-02-10 18:03:03 |
| 217 | 107 | ava.chen@oldmail.com | Boston | 2024-02-12 19:25:22 |
| 218 | 103 | null | Portland | 2024-02-18 21:55:51 |
| 219 | 102 | yara.hassan@example.com | Phoenix | 2024-02-03 23:40:39 |
| 220 | 101 | quinn.foster@example.com | Austin | 2024-02-14 10:47:23 |
| 221 | 103 | gabe.kim@example.com | San Francisco | 2024-02-09 23:54:12 |
| 222 | 102 | ava.chen@acmehouse.com | Houston | 2024-01-30 02:19:10 |
| 223 | 103 | olivia.reed@example.com | Nashville | 2024-02-12 20:50:23 |
| 224 | 107 | yara.hassan@example.com | Portland | 2024-02-09 12:25:32 |
| 225 | 104 | cara.ng@example.com | Dallas | 2024-02-04 12:57:06 |
| 226 | 105 | hana.singh@example.com | Portland | 2024-02-01 10:25:58 |
| 227 | 104 | gabe.kim@example.com | Sacramento | 2024-02-18 13:37:21 |
| customer_id | resolved_email | resolved_city | discrepancy_flag |
|---|---|---|---|
| 101 | ava.chen@acmehouse.com | Seattle | conflict |
| 102 | ben.lee@example.com | Portland | match_or_missing |
| 103 | cara.ng@example.com | Portland | conflict |
| 104 | gabe.kim@example.com | Sacramento | conflict |
| 105 | ella.park@example.com | San Jose | match_or_missing |
| 106 | farah.ali@example.com | Austin | match_or_missing |
| 107 | gabe.kim@example.com | Boston | conflict |
| 108 | hana.singh@example.com | Dallas | match_or_missing |
| 110 | jules.martin@example.com | Miami | conflict |
