Task
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.
Schema
| table | column | type | description |
|---|
| contacts | contact_id | INT | Primary key for the contact |
| contacts | account_id | INT | Related account identifier |
| contacts | full_name | VARCHAR(255) | Raw contact name from CRM |
| contacts | email | VARCHAR(255) | Raw email address; may contain blanks or mixed case |
| contacts | phone | VARCHAR(50) | Raw phone number; may be inconsistently formatted |
| contacts | status | VARCHAR(50) | CRM status entered by users |
| accounts | account_id | INT | Primary key for the account |
| accounts | account_name | VARCHAR(255) | Account name |
| accounts | owner_team | VARCHAR(100) | Owning team for the account |
| activities | activity_id | INT | Primary key for the activity |
| activities | contact_id | INT | Contact associated with the activity |
| activities | activity_date | DATE | Date of the activity |
| activities | activity_type | VARCHAR(50) | Type of activity logged |
Sample data
| accounts.account_id | accounts.account_name | accounts.owner_team |
|---|---:|---|---|
| 10 | Northwind | Enterprise |
| 11 | Apex Labs | Mid-Market |
| 12 | Orion Co | SMB |
| activities.activity_id | activities.contact_id | activities.activity_date | activities.activity_type |
|---|
| 100 | 1 | 2024-05-01 | call |
| 101 | 1 | 2024-05-10 | email |
| 102 | 2 | 2024-05-03 | demo |
| 103 | 4 | 2024-04-28 | note |
Expected output
| contact_id | account_name | normalized_name | normalized_email | latest_activity_date | data_quality_status |
|---|
| 1 | Northwind | Ava Chen | ava.chen@example.com | 2024-05-10 | valid |
| 2 | Northwind | Ben Ortiz | NULL | 2024-05-03 | needs_review |
| 3 | Apex Labs | NULL | carla@example.com | NULL | invalid |
| 4 | Orion Co | Dana Lee | dana.lee@example.com | 2024-04-28 | needs_review |
