Welcome to the SQL screen.
The question is on your right: Normalize Messy CRM Contact Data. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |