Problem
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
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| full_name | VARCHAR(100) | Customer full name |
| 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 |
Practicing as: Customer Insights Analyst interview at Best BuyHi, I'll play your Best Buy interviewer for the Customer Insights Analyst role. Candidates describe these interviews as mostly positive and on the easier side, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
