Your question is Normalize Messy CRM Contact Data. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
You are given a messy CRM with inconsistent data entry standards across contacts, accounts, and activity logs. Write a PostgreSQL query that returns one row per contact with standardized fields, a data quality status, and the most recent activity date. Treat blank strings as missing values, normalize email addresses to lowercase, and classify each contact as valid, needs_review, or invalid based on the available data.
| Column | Type | Description |
|---|---|---|
| contact_idPK | INT | Primary key for the contact |
| account_id | INT | Foreign key to the related account |
| full_name | VARCHAR(255) | Raw contact name entered in the CRM |
| VARCHAR(255) | Raw email address with inconsistent casing or blanks | |
| phone | VARCHAR(50) | Raw phone number with inconsistent formatting |
| status | VARCHAR(50) | User-entered CRM status |
| Column | Type | Description |
|---|---|---|
| account_idPK | INT | Primary key for the account |
| account_name | VARCHAR(255) | Account name |
| owner_team | VARCHAR(100) | Owning team for the account |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Primary key for the activity |
| contact_id | INT | Foreign key to the related contact |
| activity_date | DATE | Date when the activity occurred |
| activity_type | VARCHAR(50) | Type of activity logged |