Layoff Data Exploratory Project: Insights Through SQL Analysis

This SQL exploratory data analysis project delves into layoff trends across companies, industries, and countries. By ranking total layoffs, identifying top-performing entities, and analyzing trends over time, this project uncovers actionable insights into workforce patterns. Utilizing advanced SQL techniques like CTEs, aggregations, and window functions, it highlights expertise in summarizing complex datasets for strategic decision-making and workforce management.

Link to Code:

https://github.com/jpg109/jp_portfolio/blob/main/SQL%20Data%20Exploratory%20-%20Layoff.sql

Background:

After cleaning the 'Layoff Data' in the previous project, this exploratory analysis aims to uncover actionable insights.

Process:

Using MySQL, we identified trends in layoffs across companies, industries, countries, and years shown in the code below.

-- Step 1: Previewing Cleaned Data

Purpose: To ensure the data from the 'layoffs_staging2' table is ready for analysis.

This step verifies the availability and structure of required fields.

-- Step 2: Ranking Companies by Total Layoffs

Purpose: To identify companies with the highest layoffs.

We use DENSE_RANK to assign unique rankings based on the total number of employees laid off.

-- Querying individual layoff totals:

SELECT company, industry, total_laid_off 
FROM layoffs_staging_2
ORDER BY total_laid_off DESC;

-- Calculating cumulative layoffs for each company:

SELECT company, SUM(total_laid_off) as company_total
FROM layoffs_staging_2
GROUP BY company
HAVING company_total iS NOT NULL
ORDER BY company ASC;

-- Assigning rankings to companies based on cumulative layoffs:

WITH total_company_laid_off_rank AS
(
SELECT company, SUM(total_laid_off) AS total_company_laid_off
FROM layoffs_staging_2
GROUP BY company
)
SELECT 
company, total_company_laid_off, DENSE_RANK() OVER(ORDER BY total_company_laid_off DESC) AS company_rank
FROM total_company_laid_off_rank;

-- Step 3: Ranking Countries by Total Layoffs

Purpose: To determine the countries most affected by layoffs.

This step calculates and ranks layoffs aggregated by country.

SELECT country, SUM(total_laid_off) AS total_country_laid_off
FROM layoffs_staging_2
WHERE total_laid_off IS NOT NULL
GROUP BY country
ORDER BY total_country_laid_off DESC;

-- Using a CTE to assign rankings by country:

WITH country_laid_off AS
(
SELECT country, SUM(total_laid_off) AS total_country_laid_off
FROM layoffs_staging_2
WHERE total_laid_off IS NOT NULL
GROUP BY country
)
SELECT country, total_country_laid_off,
DENSE_RANK() OVER(ORDER BY total_country_laid_off DESC) AS country_laid_off_rank
FROM country_laid_off
ORDER BY country_laid_off_rank;

-- Step 4: Ranking Industries by Total Layoffs

Purpose: To analyze which industries faced the most layoffs.

The data is grouped by industry and ranked using DENSE_RANK.

WITH total_industry_laid_off_rank AS
(
SELECT industry, SUM(total_laid_off) AS total_industry_laid_off
FROM layoffs_staging_2
GROUP BY industry
)
SELECT 
industry, total_industry_laid_off, DENSE_RANK() OVER(ORDER BY total_industry_laid_off DESC) AS industry_rank
FROM total_industry_laid_off_rank
WHERE industry IS NOT NULL;

-- Step 5: Identifying Top Companies Each Year

Purpose: To highlight companies with the highest layoffs for each year.

This step uses DENSE_RANK with PARTITION BY to rank companies yearly.

-- Extracting yearly layoff totals by company:

SELECT company, YEAR(`date`) as years, SUM(total_laid_off) AS total_laid_off
FROM layoffs_staging_2
WHERE total_laid_off IS NOT NULL
GROUP BY company, years
ORDER BY company ASC;

-- Ranking top companies within each year:

WITH company_laid_off AS
(
SELECT company, YEAR(`date`) AS years, SUM(total_laid_off) AS total_laid_off
FROM layoffs_staging_2
WHERE total_laid_off IS NOT NULL AND YEAR(`date`) IS NOT NULL
GROUP BY company, years
),
company_ranking AS
(
SELECT *,
DENSE_RANK() OVER(PARTITION BY years ORDER BY total_laid_off DESC) AS ranking
FROM company_laid_off
)
SELECT *
FROM company_ranking
WHERE ranking <= 3
ORDER BY years;

I’d love to hear your thoughts on this project! Feel free to connect on LinkedIn or explore more of my work on Dream to Compete.