layoffs.sql

select *
from layoffs;
create table layoffs_staging
like layoffs;
select *
from layoffs_staging;
insert into layoffs_staging
select *
from layoffs;
with duplicate_cte as (
select *,
row_number() over(PARTITION BY company,location,industry,total_laid_off,percentage_laid_off,`date`,stage,country,funds_raised_millions) as row_num
from layoffs_staging
)
select*
from duplicate_cte 
where row_num > 1
;
CREATE TABLE `layoffs_staging2` (
  `company` text,
  `location` text,
  `industry` text,
  `total_laid_off` int DEFAULT NULL,
  `percentage_laid_off` text,
  `date` text,
  `stage` text,
  `country` text,
  `funds_raised_millions` int DEFAULT NULL,
  `row_num` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into layoffs_staging2
select *,
row_number() over(PARTITION BY company,location,industry,total_laid_off,percentage_laid_off,`date`,stage,country,funds_raised_millions) as row_num
from layoffs_staging;
select *
from layoffs_staging2;
delete 
from layoffs_staging2
where row_num > 1
;
select *
from layoffs_staging2
where row_num > 1
;
-- standardising data
select *
from layoffs_staging2;
select company,trim(company)
from layoffs_staging2;
update layoffs_staging2
set company = trim(company);
select *
from layoffs_staging2
where industry like 'crypto%' ;
update layoffs_staging2
set industry = 'crypto'
where industry like 'crypto%'
;
select country
from layoffs_staging2
where country like '%state%'
;
update layoffs_staging2
set country = trim(trailing '.' from country)
where country like 'united states%'
;
select `date`
from layoffs_staging2;
SET SQL_SAFE_UPDATES = 0;
update layoffs_staging
set `date` = str_to_date(`date`,'%m/%d/%Y');
select `date`
from layoffs_staging;
select 	`date`
from layoffs_staging2;
select *
from layoffs_staging2;
ALTER TABLE layoffs_staging2 DROP COLUMN `date`;
ALTER TABLE layoffs_staging2 ADD COLUMN `date` TEXT;
UPDATE layoffs_staging2 t2
JOIN layoffs_staging t1 ON t2.company = t1.company 
  AND t2.location = t1.location 
  AND t2.total_laid_off = t1.total_laid_off
SET t2.`date` = t1.`date`;
select *
from layoffs_staging2
where industry is null
or industry = ''
;
update layoffs_staging2
set industry = null
where industry = '' ;
select *
from layoffs_staging2
where company = 'Airbnb';
select t1.industry,t2.industry
from layoffs_staging2 t1
join layoffs_staging2 t2
on t1.company = t2.company
where t1.industry is null 
and t2.industry is not null;
update layoffs_staging2 t1
join layoffs_staging2 t2
on t1.company = t2.company
set t1.industry = t2.industry
where t1.industry is null 
and t2.industry is not null;
select *
from layoffs_staging2
where total_laid_off is null
and percentage_laid_off is null;
delete
from layoffs_staging2
where total_laid_off is null
and percentage_laid_off is null;
alter table layoffs_staging2
drop column row_num;
select*
from layoffs_staging2;
--exploratory data analysis
select max(total_laid_off),max(percentage_laid_off)
from layoffs_staging2;
select *
from layoffs_staging2
where percentage_laid_off = 1
order by total_laid_off desc;
with rolling_total as
(select substring(`date`,1,7) as `month`,
sum(total_laid_off ) as layoff
from layoffs_staging2
where substring(`date`,1,7) is not null
group by `month`
order by 1 asc)
select `month`,layoff,sum(layoff) over(order by `month`) as rolling_sum
from rolling_total;
select company,year(`date`),sum(total_laid_off)
from layoffs_staging2
group by company,year(`date`);
with company_year (company,years,total_laid_off) as
(
select company,year(`date`),sum(total_laid_off)
from layoffs_staging2
group by company,year(`date`)
), company_year_rank as
(select *,dense_rank() over(partition by years order by total_laid_off desc) as ranking
from company_year
where years is not null
)
select *
from company_year_rank
where ranking <= 5;