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.
Output details
- Return columns in this order:
contact_id, account_name, normalized_name, normalized_email, latest_activity_date, data_quality_status.
- Sort results by
contact_id ascending.
- Trim leading and trailing whitespace before treating names, emails, and statuses as missing or comparing them.
- Include only contacts with a matching account record.
- Use each contact's maximum
activity_date, preserving NULL when no activity exists.
- Classify as
valid only when trimmed name and email and activity exist and trimmed status equals case-insensitive active; classify as needs_review when any name, email, or activity exists, otherwise invalid.
