Data Cleaning in SQL AI Prompt

Write SQL to clean and standardize this dirty dataset.

Issues identified: {{issues}} (nulls, duplicates, format inconsistencies, outliers, invalid values)
Table: {{table}}
Database: {{database}}

1. NULL handling:
   COALESCE(column, default_value): return first non-null value
   NULLIF(column, 0): convert 0 (or empty string) to NULL
   IS NULL / IS NOT NULL: never use = NULL

   Replace NULL with 'Unknown':
   COALESCE(status, 'Unknown') AS status

   NULL-safe comparison:
   WHERE status IS DISTINCT FROM 'cancelled'
   -- equivalent to: WHERE status != 'cancelled' OR status IS NULL

2. String standardization:
   Remove extra whitespace: REGEXP_REPLACE(name, '\s+', ' ', 'g')
   Normalize email: TRIM(LOWER(email))
   Extract only digits: REGEXP_REPLACE(phone, '[^0-9]', '', 'g')
   Standardize state codes: UPPER(TRIM(state))

3. Date standardization:
   -- Handle multiple date formats in one column (PostgreSQL):
   CASE
     WHEN raw_date ~ '^\d{4}-\d{2}-\d{2}$' THEN raw_date::date
     WHEN raw_date ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(raw_date, 'MM/DD/YYYY')
     ELSE NULL
   END AS clean_date

4. Outlier detection and flagging:
   -- Flag values more than 3 standard deviations from the mean
   SELECT *,
     ABS(amount - AVG(amount) OVER ()) / NULLIF(STDDEV(amount) OVER (), 0) AS z_score
   FROM transactions
   WHERE ABS(amount - AVG(amount) OVER ()) / NULLIF(STDDEV(amount) OVER (), 0) > 3;

5. Invalid value replacement:
   CASE
     WHEN age < 0 OR age > 120 THEN NULL
     ELSE age
   END AS cleaned_age

6. Data profiling queries:
   -- Column completeness
   SELECT
     COUNT(*) AS total_rows,
     COUNT(email) AS non_null_email,
     COUNT(DISTINCT email) AS unique_emails,
     SUM(CASE WHEN email ~ '^[^@]+@[^@]+\.[^@]+$' THEN 0 ELSE 1 END) AS invalid_emails
   FROM users;

Return: SQL cleaning queries for each identified issue, data profiling queries for quality assessment, and update statements to persist the cleaned values.