data_cleaning_by_PostgreSQL.sql

-- =====================================================
-- LAYOFFS DATA CLEANING PROJECT
-- Author: Muhammad Akbar Khan
-- Role: M.S in Applied Mathematics | Data Analyst
-- Database: PostgreSQL
-- Data Source: Layoffs dataset from Kaggle
-- Purpose: Comprehensive data cleaning and standardization pipeline
-- Created: 2025-10-08
-- Last Modified: 2025-10-08
-- =====================================================

-- Create the initial raw data table
-- All columns stored as TEXT initially for flexible data ingestion
CREATE TABLE layoffs (
    company TEXT,
    location TEXT,
    industry TEXT,
    total_laid_off NUMERIC,
    percentage_laid_off NUMERIC,
    date TEXT,
    stage TEXT,
    country TEXT,
    funds_raised_millions NUMERIC
);

-- Preview raw dataset contents
SELECT *
FROM layoffs;

-- Detect duplicate records across all key attributes for initial quality profiling
SELECT DISTINCT *
FROM layoffs;

-- Use grouping to detect exact duplicates across all dataset fields
SELECT *,
    COUNT(*)
FROM layoffs
GROUP BY company,
    location,
    industry,
    total_laid_off,
    percentage_laid_off,
    date,
    stage,
    country,
    funds_raised_millions
HAVING COUNT(*) > 1
ORDER BY company;

-- Examine all companies identified with potential duplicate records
SELECT *
FROM layoffs
WHERE company IN (
        'Casper',
        'Cazoo',
        'Hibob',
        'Wildlife Studios',
        'Yahoo'
    )
ORDER BY company;

-- =====================================================
-- DATA DEDUPLICATION
-- Create a clean staging table by removing exact duplicates
-- =====================================================
CREATE TABLE layoffs_staging AS
SELECT DISTINCT *
FROM layoffs;

-- Validate creation of the staging table
SELECT *
FROM layoffs_staging;

-- Confirm that duplicate records have been removed
SELECT DISTINCT *
FROM layoffs_staging;

-- =====================================================
-- COMPANY COLUMN CLEANING
-- =====================================================
SELECT company
FROM layoffs_staging
WHERE company IS NULL
    OR company = ''
    OR company ILIKE 'NULL';

SELECT DISTINCT company
FROM layoffs_staging;

SELECT company
FROM layoffs_staging
WHERE company != TRIM(company);

UPDATE layoffs_staging
SET company = TRIM(company);

SELECT LOWER(company),
    COUNT(DISTINCT company),
    ARRAY_AGG(DISTINCT company)
FROM layoffs_staging
GROUP BY LOWER(company)
HAVING COUNT(DISTINCT company) > 1;

SELECT *
FROM layoffs_staging
WHERE company ILIKE 'appgate'
    OR company ILIKE 'bytedance'
    OR company ILIKE 'clearco'
    OR company ILIKE 'curefit'
    OR company ILIKE 'salesloft'
ORDER BY company;

UPDATE layoffs_staging
SET company = CASE
        WHEN LOWER(company) = 'appgate' THEN 'Appgate'
        WHEN LOWER(company) = 'bytedance' THEN 'ByteDance'
        WHEN LOWER(company) = 'clearco' THEN 'Clearco'
        WHEN LOWER(company) = 'curefit' THEN 'Curefit'
        WHEN LOWER(company) = 'salesloft' THEN 'Salesloft'
        ELSE company
    END;

SELECT *
FROM layoffs_staging AS t1
    INNER JOIN layoffs_staging AS t2 ON t1.company < t2.company
WHERE SIMILARITY(t1.company, t2.company) > 0.6
ORDER BY t1.company,
    t2.company;

UPDATE layoffs_staging
SET company = 'Impossible Foods'
WHERE company = 'Impossible Foods copy';

-- =====================================================
-- LOCATION COLUMN CLEANING
-- =====================================================
SELECT location
FROM layoffs_staging
WHERE location IS NULL
    OR location = ''
    OR location ILIKE 'NULL';

SELECT DISTINCT location
FROM layoffs_staging
ORDER BY location;

SELECT location
FROM layoffs_staging
WHERE location != TRIM(location);

SELECT LOWER(location),
    COUNT(DISTINCT location)
FROM layoffs_staging
GROUP BY LOWER(location)
HAVING COUNT(DISTINCT location) > 1;

SELECT t1.location AS location_1,
    t2.location AS location_2
FROM layoffs_staging AS t1
    INNER JOIN layoffs_staging AS t2 ON t1.location < t2.location
WHERE SIMILARITY(t1.location, t2.location) > 0.4;

SELECT *
FROM layoffs_staging
WHERE location ILIKE '%sseldorf'
    OR location ILIKE 'Malm%'
ORDER BY location;

UPDATE layoffs_staging
SET location = CASE
        WHEN location = 'Dusseldorf' THEN 'Düsseldorf'
        WHEN location = 'Malmo' THEN 'Malmö'
        ELSE location
    END;

-- =====================================================
-- INDUSTRY COLUMN CLEANING
-- =====================================================
SELECT industry
FROM layoffs_staging
WHERE industry IS NULL
    OR industry = ''
    OR industry ILIKE 'NULL';

UPDATE layoffs_staging
SET industry = NULL
WHERE industry = ''
    OR industry ILIKE 'NULL';

SELECT t1.company,
    t1.industry AS industry_1,
    t2.industry AS industry_2
FROM layoffs_staging AS t1
    INNER JOIN layoffs_staging AS t2 ON t1.company = t2.company
WHERE t1.industry IS NULL
    AND t2.industry IS NOT NULL
ORDER BY t1.company;

SELECT *
FROM layoffs_staging
WHERE company IN ('Airbnb', 'Carvana', 'Juul')
ORDER BY company;

UPDATE layoffs_staging AS t1
SET industry = t2.industry
FROM layoffs_staging AS t2
WHERE t1.company = t2.company
    AND t1.industry IS NULL
    AND t2.industry IS NOT NULL;

-- =====================================================
-- DATE COLUMN CLEANING AND CONVERSION
-- =====================================================
SELECT date
FROM layoffs_staging
WHERE date IS NULL
    OR date = ''
    OR date ILIKE 'NULL';

SELECT date
FROM layoffs_staging;

SELECT date,
    TO_DATE(date, 'MM/DD/YYYY')
FROM layoffs_staging;

ALTER TABLE layoffs_staging
ALTER COLUMN date TYPE DATE USING TO_DATE(date, 'MM/DD/YYYY');

SELECT date
FROM layoffs_staging
WHERE date IS NULL;

DELETE FROM layoffs_staging
WHERE date IS NULL;

-- =====================================================
-- STAGE COLUMN CLEANING
-- =====================================================
SELECT stage
FROM layoffs_staging
WHERE stage IS NULL
    OR stage = ''
    OR stage ILIKE 'NULL';

SELECT DISTINCT stage
FROM layoffs_staging
ORDER BY stage;

-- =====================================================
-- COUNTRY COLUMN CLEANING
-- =====================================================
SELECT country
FROM layoffs_staging
WHERE country IS NULL
    OR country = ''
    OR country ILIKE 'NULL';

SELECT DISTINCT country
FROM layoffs_staging
ORDER BY country;

SELECT *
FROM layoffs_staging
WHERE country ILIKE 'United States%'
ORDER BY country DESC;

UPDATE layoffs_staging
SET country = 'United States'
WHERE country = 'United States.';

-- =====================================================
-- FUNDS_RAISED_MILLIONS COLUMN
-- =====================================================
SELECT funds_raised_millions
FROM layoffs_staging
WHERE funds_raised_millions IS NULL;

-- =====================================================
-- FINAL DATA QUALITY CHECKS
-- =====================================================
SELECT *
FROM layoffs_staging
WHERE total_laid_off IS NULL
    AND percentage_laid_off IS NULL;

DELETE FROM layoffs_staging
WHERE total_laid_off IS NULL
    AND percentage_laid_off IS NULL;

-- =====================================================
-- FINAL VALIDATION AND DATA QUALITY ASSESSMENT
-- =====================================================
SELECT COUNT(*) AS final_clean_records
FROM layoffs_staging;

SELECT COUNT(*)
FROM layoffs_staging
WHERE company IS NULL
    OR date IS NULL;

SELECT COUNT(*) AS total_records,
    COUNT(DISTINCT company) AS unique_companies,
    COUNT(DISTINCT industry) AS unique_industries,
    COUNT(DISTINCT country) AS unique_countries,
    COUNT(DISTINCT location) AS unique_locations,
    SUM(
        CASE
            WHEN total_laid_off IS NOT NULL THEN 1
            ELSE 0
        END
    ) AS records_with_layoff_counts,
    SUM(
        CASE
            WHEN percentage_laid_off IS NOT NULL THEN 1
            ELSE 0
        END
    ) AS records_with_percentages,
    MIN(date) AS earliest_layoff_date,
    MAX(date) AS latest_layoff_date
FROM layoffs_staging;

-- =====================================================
-- FINAL CLEANED TABLE CREATION
-- Author: Muhammad Akbar Khan
-- Purpose: Generate the final cleaned and standardized dataset
-- =====================================================
DROP TABLE IF EXISTS layoffs_cleaned;

CREATE TABLE layoffs_cleaned AS
SELECT
    company,
    location,
    industry,
    total_laid_off,
    percentage_laid_off,
    date,
    stage,
    country,
    funds_raised_millions
FROM layoffs_staging;

SELECT COUNT(*) AS total_clean_records
FROM layoffs_cleaned;

SELECT COUNT(*) AS null_critical_fields
FROM layoffs_cleaned
WHERE company IS NULL
    OR date IS NULL
    OR country IS NULL;

SELECT *
FROM layoffs_cleaned
LIMIT 20;