Check Out Some of My Works
The aim of this project is to clean and standardize a dataset containing layoff information from various companies. The project involves removing duplicates, handling null values, formatting data, and performing other data cleansing tasks to ensure data consistency and readiness for analysis. The project also aims to conduct an Exploratory Data Analysis (EDA) on global layoff data to uncover trends, patterns, and insights such as the largest layoffs by companies, locations, industries, and countries over time. The goal is to understand the magnitude and distribution of layoffs across different factors.
SQL: Data cleaning and transformation using MySQL.
CTE (Common Table Expressions): For identifying and deleting duplicates.
Window Functions: Usage of ROW_NUMBER() to handle duplicates and for subqueries and complex query chains.
String Functions: TRIM() to clean up textual data.
Date Functions: STR_TO_DATE() to reformat and standardize date values.
Aggregate Functions: SUM(), MAX(), MIN().
Analytical SQL Concepts: Grouping, ranking, rolling totals, and temporal data analysis are key components of this project.
Creation of Staging Table:
A copy of the original dataset is created in a staging table to preserve raw data during cleaning operations.
Duplicate Removal:
Identification and deletion of duplicate records using ROW_NUMBER() with PARTITION BY.
Checked for specific duplicates, such as for companies "Oda" and "Casper."
Data Formatting and Standardization:
Trimming whitespace from the company names using TRIM().
Updating incorrect values in the industry column (e.g., replacing a URL with 'Newspaper').
Formatting the date column and altering its datatype to DATE.
Null and Blank Values Handling:
Identification of null or blank values in total_laid_off and percentage_laid_off columns.
Deletion of rows with both columns as blank or null.
Data Type Corrections:
Modified columns like date and funds_raised to appropriate data types.
Data Quality Checks:
Performed checks on industry, location, stage, country, and other columns to identify inconsistencies and rectify them.
Ranking by Layoff Magnitude:
Largest Layoffs by Company: Queries are used to identify which companies had the largest single-day layoffs and which companies had the highest total layoffs.
Layoffs by Location and Country: Grouping by location and country to find areas most impacted by layoffs.
Layoffs by Industry: Understanding the layoffs distributed by industry, revealing which sectors are most affected.
Layoffs by Funding Stage: Grouping and ranking layoffs by funding stage (e.g., Series A, B, etc.).
Year-over-Year Layoff Trends:
Layoff Trends by Year: Year-over-year trends are identified, showing how layoffs have evolved annually.
Top 3 Companies by Layoffs per Year: Using a combination of CTEs and DENSE_RANK(), the project identifies the top three companies with the most layoffs each year.
Rolling Layoff Totals:
Rolling Total Layoffs Per Month: A rolling total of layoffs per month is calculated, providing insight into how layoffs accumulate over time.
CTE-Based Analysis: A CTE is used to generate rolling totals, which can be queried for more flexible insights.
The cleaned dataset is consistent, free from duplicates, and has properly formatted data, making it ready for further analysis or reporting. This ensures that any insights derived from the data are based on accurate and reliable information, improving decision-making around company layoffs.
Key Insights on Layoffs: The EDA uncovered valuable insights, such as companies and industries most impacted by layoffs, the locations with the highest layoffs, and trends in layoffs over time.
Identification of Outliers: Outliers, such as companies laying off 100% of their employees, were identified, highlighting sectors under stress during specific periods.
Temporal Analysis: Rolling totals of layoffs provided a view of how layoffs accumulate and when spikes occur, allowing for better understanding of economic impacts.
