SQL Data Cleaning & Exploratory Data Analysis

This project focuses on the comprehensive data cleaning and exploratory data analysis (EDA) I performed on a dataset of company layoffs. Through this project, I honed several key data-analytics skills, including SQL data cleaning, handling missing values, removing duplicates, and performing detailed exploratory analysis using complex queries. The techniques I used in this project are directly applicable to many real-world data analysis tasks, making it an important step in my journey as a data analyst.

Data Cleaning Techniques

Some of the specific skills and tasks I performed included:

• Handling Duplicates: Used `ROW_NUMBER()` and `PARTITION BY` to identify and remove duplicate rows from the dataset.
• Standardizing Data: Cleaned inconsistent data entries, such as trimming spaces from text and formatting date fields.
• Dealing with Missing Values: Identified and handled null values in critical columns like `total_laid_off` and `percentage_laid_off` to ensure accurate analysis.
• Dropping Unnecessary Columns: Removed columns that provided no value to the analysis, streamlining the dataset for further exploration.

Key SQL Queries for Data Cleaning

Below is a sample of the SQL queries used during the data cleaning process:

-- Remove duplicates using row_number() 
WITH duplicate_cte AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY company, location, industry ORDER BY date) AS row_num
    FROM layoffs_staging
)
DELETE FROM duplicate_cte WHERE row_num > 1;
					

Exploratory Data Analysis (EDA)

After cleaning the data, I performed a series of **exploratory analysis** tasks to extract meaningful insights from the layoffs data. This involved grouping data by key attributes, identifying trends over time, and investigating correlations between layoffs and other variables. The following tasks were performed:

• Company-wise Layoff Trends: Aggregated data by company to determine the total number of layoffs for each organization and analyzed patterns.
• Yearly and Monthly Layoff Analysis: Analyzed the data over time by extracting the year and month from the date field and performing a rolling total analysis.
• Location-based Insights: Investigated the layoffs by location and country to identify geographic trends and outliers.
• Industry Analysis: Examined layoffs across different industries, with a focus on high-impact sectors like technology and finance.

Key SQL Queries for EDA

Below is a sample of the SQL queries used during the exploratory analysis phase:

-- Yearly analysis of layoffs by company
SELECT company, YEAR(date), SUM(total_laid_off)
FROM world_layoffs.layoffs_staging2
GROUP BY company, YEAR(date)
ORDER BY company ASC;

-- Rolling total of layoffs by month
WITH rolling_total AS (
    SELECT SUBSTRING(date, 1, 7) AS month, SUM(total_laid_off) AS total_off
    FROM world_layoffs.layoffs_staging2
    GROUP BY month
)
SELECT month, total_off, SUM(total_off) OVER (ORDER BY month) AS rolling_total
FROM rolling_total;
					

Skills Developed

Through this project, I developed and refined the following key **data analytics** skills:

• Advanced SQL Querying: Writing complex queries with `JOIN`, `GROUP BY`, `PARTITION BY`, `WITH`, and window functions.
• Data Cleaning & Preprocessing: Efficiently handling missing values, removing duplicates, and ensuring the dataset is standardized for analysis.
• Exploratory Data Analysis (EDA): Conducting in-depth analysis of data trends, and identifying significant insights for business decisions.
• Data Transformation: Using SQL functions to manipulate data and generate useful metrics for decision-making.

Conclusion

This project provided an opportunity to apply my skills in data cleaning and analysis, using real-world data to generate insights into industry-wide trends related to company layoffs. The techniques I used in this project are directly applicable to many real-world data analysis tasks, making it an important step in my journey as a data analyst.

Code Samples & GitHub Repository

You can view the full code and additional SQL-based data analytics projects on my GitHub repository. Please visit the link below for access: