Task
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_id | 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 |
Sample data
Expected output
| customer_id | data_quality_status |
|---|
| 101 | Complete |
| 102 | Incomplete |
| 103 | Incomplete |
| 104 | Incomplete |
| 105 | Incomplete |
| 106 | Inconsistent |
| 107 | Inconsistent |
| 108 | Complete |
| 109 | Incomplete |
| 110 | Inconsistent |
