Cleaning Data with SQL

-- 1. DETECTING NULLS
-- Find all customers missing an email address
SELECT * FROM customers
WHERE email IS NULL;

-- 2. FILLING NULLS (SELECT)
-- COALESCE returns the first non-null value
-- This doesn't change the database, it just formats the output
SELECT 
    id, 
    first_name, 
    COALESCE(phone, 'No Phone on File') AS phone_status
FROM customers;

-- 3. UPDATING NULLS (Permanent Fix)
-- Permanently replace NULL countries with 'Unknown'
UPDATE customers
SET country = 'Unknown'
WHERE country IS NULL;

-- 4. DELETING NULLS
-- Delete records that are completely useless (e.g., missing ID or email)
DELETE FROM customers
WHERE email IS NULL;

-- 1. WHITESPACE CLEANING
-- TRIM() removes leading and trailing spaces
UPDATE employees
SET last_name = TRIM(last_name);

-- 2. CASE STANDARDIZATION
-- Make names Title Case (varies slightly by SQL dialect, this is PostgreSQL/MySQL approach)
UPDATE employees
SET first_name = CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)));
-- Simple UPPER/LOWER:
-- SET state = UPPER(state);

-- 3. STRING REPLACEMENT
-- Remove hyphens from phone numbers
UPDATE contacts
SET phone_number = REPLACE(phone_number, '-', '');
-- '555-123-4567' becomes '5551234567'

-- 4. REGEX IN SQL (PostgreSQL example)
-- Remove all non-numeric characters from a phone string
UPDATE contacts
SET phone_number = REGEXP_REPLACE(phone_number, '\D', '', 'g');

-- Convert string '2024-01-15' to an actual DATE type in output
SELECT CAST(signup_date AS DATE) FROM users;
-- OR (PostgreSQL syntax)
SELECT signup_date::DATE FROM users;

-- Standardize boolean flags
-- If active status is stored as 'Y', 'N', 'Yes', '1', '0'
UPDATE users
SET is_active = CASE 
    WHEN status IN ('Y', 'Yes', '1') THEN 1
    WHEN status IN ('N', 'No', '0') THEN 0
    ELSE 0
END;

-- Identifying duplicates (Using Group By)
-- Find emails that appear more than once
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Deleting duplicates (Keeping the latest one based on updated_at)
-- This uses a Common Table Expression (CTE) and Window Function
WITH RankedDuplicates AS (
    SELECT 
        id,
        ROW_NUMBER() OVER(
            PARTITION BY email 
            ORDER BY updated_at DESC
        ) as row_num
    FROM users
)
DELETE FROM users
WHERE id IN (
    SELECT id 
    FROM RankedDuplicates 
    WHERE row_num > 1
);

-- Execute a multi-step cleaning script inside a transaction
-- This ensures that if one step fails, the database rolls back to the original state

BEGIN TRANSACTION;

-- 1. Remove garbage rows (No name AND no email)
DELETE FROM customer_leads 
WHERE name IS NULL AND email IS NULL;

-- 2. Standardize Emails
UPDATE customer_leads
SET email = LOWER(TRIM(email));

-- 3. Handle NULL phone numbers
UPDATE customer_leads
SET phone = 'N/A'
WHERE phone IS NULL;

-- 4. Standardize State abbreviations
UPDATE customer_leads
SET state = 'NY' WHERE state IN ('New York', 'ny', 'N.Y.');

-- 5. Remove Duplicates (keep the lowest ID / oldest record)
WITH Dupes AS (
    SELECT id, ROW_NUMBER() OVER(PARTITION BY email ORDER BY id ASC) as rn
    FROM customer_leads
)
DELETE FROM customer_leads 
WHERE id IN (SELECT id FROM Dupes WHERE rn > 1);

COMMIT;