Cleaning Messy Data

How would you clean a messy dataset before analysis?

Write a PostgreSQL query that standardizes consultant and assignment fields, validates dates and hours, removes unusable assignment records, and combines valid assignments with their consultants. Preserve valid NULL email or region values.

Output

1. One row per valid assignment, ordered by start_date ascending and then assignment_id ascending.
2. Return assignment_id, consultant_id, consultant_name, email, region, status, start_date, and hours.
3. Exclude records with an unknown status, invalid date, invalid or negative hours, missing consultant matches, or missing consultant names.