Profile Incomplete Customer Records | Dataford Interview Questions

You are given a customer profile table from Best Buy account data. Some records are incomplete or inconsistent: email may be missing, phone may be blank, city may be null, and loyalty tier values may not follow the expected standard. Write a PostgreSQL query that returns one row per customer_id with a data_quality_status of Complete, Incomplete, or Inconsistent. Treat a record as Incomplete if email, phone, or city is null or an empty string. Treat a record as Inconsistent if loyalty_tier is not one of My Best Buy, My Best Buy Plus, or My Best Buy Total. If both issues exist, label the record as Inconsistent. Return the results ordered by customer_id.

Schema

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
full_name	VARCHAR(100)	Customer full name
email	VARCHAR(150)	Customer email address
phone	VARCHAR(20)	Customer phone number
city	VARCHAR(100)	Customer city
loyalty_tier	VARCHAR(50)	Best Buy loyalty membership tier

Your question is Profile Incomplete Customer Records. Start with the requirements and the one table 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

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
full_name	VARCHAR(100)	Customer full name
email	VARCHAR(150)	Customer email address
phone	VARCHAR(20)	Customer phone number
city	VARCHAR(100)	Customer city
loyalty_tier	VARCHAR(50)	Best Buy loyalty membership tier

Your question is Profile Incomplete Customer Records. Start with the requirements and the one table 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.