Resolve Conflicting Customer Attributes | Dataford Interview Questions

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_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

Your question is Resolve Conflicting Customer Attributes. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

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

Your question is Resolve Conflicting Customer Attributes. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.