SQL Data Cleaning Techniques — Handling Nulls, Duplicates, Inconsistent Values & Quality Checks

-- Count NULLs in every column in one query
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN customer_id   IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
    SUM(CASE WHEN email         IS NULL THEN 1 ELSE 0 END) AS null_email,
    SUM(CASE WHEN order_date    IS NULL THEN 1 ELSE 0 END) AS null_order_date,
    SUM(CASE WHEN revenue       IS NULL THEN 1 ELSE 0 END) AS null_revenue,
    SUM(CASE WHEN city          IS NULL THEN 1 ELSE 0 END) AS null_city
FROM orders;

-- COALESCE returns the first non-NULL value from the list
-- Works in all major databases (MySQL, PostgreSQL, SQL Server, BigQuery)
SELECT
    order_id,
    COALESCE(discount_pct, 0)          AS discount_pct,   -- NULL → 0
    COALESCE(city, 'Unknown')           AS city,            -- NULL → 'Unknown'
    COALESCE(phone, email, 'No contact') AS contact         -- first non-NULL wins
FROM orders;

-- SQL Server shorthand (single-column only):
-- ISNULL(discount_pct, 0)

-- Oracle / older databases:
-- NVL(discount_pct, 0)

-- CORRECT — use IS NULL / IS NOT NULL
SELECT * FROM orders WHERE email IS NULL;
SELECT * FROM orders WHERE email IS NOT NULL;

-- WRONG — this returns 0 rows even when NULLs exist
-- SELECT * FROM orders WHERE email = NULL;   ← never use this

-- Exclude rows where ANY key column is NULL
SELECT *
FROM orders
WHERE customer_id IS NOT NULL
  AND order_date   IS NOT NULL
  AND revenue      IS NOT NULL;

-- COUNT(*) counts rows including NULLs
-- COUNT(column) counts only non-NULL values in that column
SELECT
    COUNT(*)         AS total_rows,          -- includes rows with NULL revenue
    COUNT(revenue)   AS rows_with_revenue,   -- excludes NULL revenue rows
    AVG(revenue)     AS avg_revenue,         -- AVG ignores NULLs automatically
    SUM(revenue)     AS total_revenue        -- SUM ignores NULLs automatically
FROM orders;

-- To include NULLs in AVG (treat NULL as 0):
SELECT AVG(COALESCE(revenue, 0)) AS avg_revenue_incl_nulls
FROM orders;

-- Find duplicate order_ids
SELECT order_id, COUNT(*) AS occurrences
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Full exact-row duplicates
SELECT *, COUNT(*) AS occurrences
FROM orders
GROUP BY order_id, customer_id, order_date, revenue, city
HAVING COUNT(*) > 1;

-- Keep the latest record per customer (most recent order_date wins)
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS rn
    FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

-- For exact duplicates — keep any one copy:
WITH deduped AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY order_id, customer_id, order_date
               ORDER BY (SELECT NULL)   -- arbitrary tiebreak
           ) AS rn
    FROM orders
)
SELECT * FROM deduped WHERE rn = 1;

-- SQL Server / PostgreSQL — delete keeping lowest ctid/rowid
WITH ranked AS (
    SELECT ctid,
           ROW_NUMBER() OVER (
               PARTITION BY order_id
               ORDER BY ctid
           ) AS rn
    FROM orders
)
DELETE FROM orders
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

-- MySQL — using self-join
DELETE t1
FROM orders t1
INNER JOIN orders t2
    ON t1.order_id = t2.order_id
    AND t1.id > t2.id;   -- keep the row with the lower auto-increment id

SELECT
    -- Trim whitespace
    TRIM(city)                         AS city_trimmed,

    -- Standardise case
    UPPER(TRIM(city))                  AS city_upper,
    LOWER(TRIM(city))                  AS city_lower,

    -- Title case (initcap — PostgreSQL / Oracle)
    INITCAP(LOWER(TRIM(city)))         AS city_title,

    -- Replace inconsistent values
    CASE
        WHEN LOWER(TRIM(city)) IN ('bangalore', 'bengaluru', 'blr')
             THEN 'Bengaluru'
        WHEN LOWER(TRIM(city)) IN ('mumbai', 'bombay')
             THEN 'Mumbai'
        WHEN LOWER(TRIM(city)) IN ('delhi', 'new delhi', 'ndls')
             THEN 'Delhi'
        ELSE INITCAP(LOWER(TRIM(city)))
    END                                AS city_clean,

    -- Remove special characters (PostgreSQL REGEXP_REPLACE)
    REGEXP_REPLACE(phone, '[^0-9]', '', 'g')  AS phone_digits_only,

    -- Extract domain from email
    SUBSTRING(email FROM POSITION('@' IN email) + 1)  AS email_domain

FROM customers;

-- Convert text stored as date string to a proper DATE type
-- PostgreSQL
SELECT TO_DATE(order_date_text, 'DD/MM/YYYY')  AS order_date
FROM orders;

-- SQL Server
SELECT CONVERT(DATE, order_date_text, 103)     AS order_date   -- 103 = DD/MM/YYYY
FROM orders;

-- MySQL
SELECT STR_TO_DATE(order_date_text, '%d/%m/%Y') AS order_date
FROM orders;

-- BigQuery
SELECT PARSE_DATE('%d/%m/%Y', order_date_text)  AS order_date
FROM orders;

-- ── Find impossible dates (future dates, dates before business started) ──
SELECT order_id, order_date
FROM orders
WHERE order_date > CURRENT_DATE             -- future dates
   OR order_date < '2015-01-01'            -- before company existed
   OR order_date IS NULL;                  -- missing dates

-- ── Fix dates where day and month may be swapped ──
-- (e.g. 07/06/2024 could be July 6 or June 7)
-- Profile first:
SELECT
    EXTRACT(MONTH FROM order_date) AS month_num,
    COUNT(*)
FROM orders
GROUP BY 1
ORDER BY 1;
-- If any month has suspiciously 0 or very low counts, day/month swap is likely.

-- Method 1: Simple boundary check (domain knowledge)
SELECT * FROM orders
WHERE revenue < 0          -- negative revenue is impossible
   OR revenue > 1000000    -- flag orders over ₹10L for review
   OR quantity = 0;        -- zero-quantity orders are likely errors

-- Method 2: IQR (Interquartile Range) method — statistical outliers
WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) AS q3
    FROM orders
),
bounds AS (
    SELECT
        q1 - 1.5 * (q3 - q1) AS lower_fence,
        q3 + 1.5 * (q3 - q1) AS upper_fence
    FROM stats
)
SELECT o.*
FROM orders o
CROSS JOIN bounds b
WHERE o.revenue < b.lower_fence
   OR o.revenue > b.upper_fence;

-- Method 3: Z-score (flag values > 3 standard deviations from mean)
WITH stats AS (
    SELECT AVG(revenue) AS mean_rev, STDDEV(revenue) AS std_rev
    FROM orders
)
SELECT o.*, ABS(o.revenue - s.mean_rev) / s.std_rev AS z_score
FROM orders o, stats s
WHERE ABS(o.revenue - s.mean_rev) / s.std_rev > 3
ORDER BY z_score DESC;

-- ── DAILY DATA QUALITY REPORT ──
SELECT 'orders' AS table_name, 'total_rows'         AS check_name,
       COUNT(*) AS value, NULL AS flag
FROM orders

UNION ALL SELECT 'orders', 'null_customer_id',
       SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END),
       CASE WHEN SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) > 0
            THEN '⚠️ FAIL' ELSE '✅ PASS' END
FROM orders

UNION ALL SELECT 'orders', 'duplicate_order_ids',
       COUNT(*) - COUNT(DISTINCT order_id),
       CASE WHEN COUNT(*) - COUNT(DISTINCT order_id) > 0
            THEN '⚠️ FAIL' ELSE '✅ PASS' END
FROM orders

UNION ALL SELECT 'orders', 'negative_revenue',
       SUM(CASE WHEN revenue < 0 THEN 1 ELSE 0 END),
       CASE WHEN SUM(CASE WHEN revenue < 0 THEN 1 ELSE 0 END) > 0
            THEN '⚠️ FAIL' ELSE '✅ PASS' END
FROM orders

UNION ALL SELECT 'orders', 'future_order_dates',
       SUM(CASE WHEN order_date > CURRENT_DATE THEN 1 ELSE 0 END),
       CASE WHEN SUM(CASE WHEN order_date > CURRENT_DATE THEN 1 ELSE 0 END) > 0
            THEN '⚠️ FAIL' ELSE '✅ PASS' END
FROM orders

ORDER BY flag DESC, check_name;

WITH
-- Step 1: Remove exact duplicates
deduped AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM raw_orders
),

-- Step 2: Apply all cleaning transformations
cleaned AS (
    SELECT
        order_id,
        customer_id,
        TRIM(INITCAP(LOWER(city)))              AS city,
        COALESCE(discount_pct, 0)               AS discount_pct,
        CASE
            WHEN revenue < 0 THEN NULL           -- flag negatives as unknown
            ELSE revenue
        END                                      AS revenue,
        CASE
            WHEN order_date > CURRENT_DATE THEN NULL  -- remove future dates
            ELSE order_date
        END                                      AS order_date,
        LOWER(TRIM(status))                      AS status
    FROM deduped
    WHERE rn = 1                                 -- keep latest per order_id
      AND customer_id IS NOT NULL                -- drop orphaned records
),

-- Step 3: Filter to valid complete records
final AS (
    SELECT *
    FROM cleaned
    WHERE order_date IS NOT NULL
      AND revenue    IS NOT NULL
)

SELECT * FROM final;