Resolve Conflicting Customer Attributes

MediumSQL · PostgreSQL

Problem

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'.

Output details

Return columns in this order: customer_id, resolved_email, resolved_city, discrepancy_flag.
Order rows by customer_id ascending.
Trim surrounding whitespace from email and city values before treating empty strings as missing or comparing values.
When multiple checkout records share a customer, use the greatest updated_at, breaking ties with greatest checkout_id.

Schema

crm_customers

Column	Type	Description
customer_idPK	INT	Customer identifier in CRM
full_name	VARCHAR(100)	Customer full name
email	VARCHAR(255)	Email stored in CRM
city	VARCHAR(100)	City stored in CRM
updated_at	TIMESTAMP	Last CRM update timestamp

checkout_customers

Column	Type	Description
checkout_idPK	INT	Checkout profile row identifier
customer_id	INT	Customer identifier linked to CRM
email	VARCHAR(255)	Email captured in checkout
city	VARCHAR(100)	City captured in checkout
updated_at	TIMESTAMP	Last checkout update timestamp

Tablescrm_customerscheckout_customers