datacleaning.sql

-- DATA CLEANING

-- WORLD LAYOFFS DATA

-- selecting everything from layoffs table

select * 
from layoffs;


-- selecting date from layoffs table

select layoff_date 
from layoffs;


-- converting date to YYYY-MM-DD format

select
layoff_date,
substr(layoff_date, instr(layoff_date, '/') + 1) AS rest,
date(
substr(layoff_date, -4) || '-' ||
printf('%02d', substr(layoff_date, 1, instr(layoff_date, '/') - 1)) || '-' ||
printf(
'%02d',
substr(
substr(layoff_date, instr(layoff_date, '/') + 1),
1,
instr(substr(layoff_date, instr(layoff_date, '/') + 1), '/') - 1
))) as converted_date
from layoffs;


-- updating the date format 

update layoffs
set layoff_date = date(
substr(layoff_date, -4) || '-' ||
printf('%02d', substr(layoff_date, 1, instr(layoff_date, '/') - 1)) || '-' ||
printf(
'%02d',
substr(
substr(layoff_date, instr(layoff_date, '/') + 1),
1,
instr(substr(layoff_date, instr(layoff_date, '/') + 1), '/') - 1
)))
where layoff_date like '%/%/%';


-- checking the result

select * 
from layoffs;


-- updating the null text to null

UPDATE layoffs_staging2
SET layoff_date = NULL
WHERE layoff_date = 'NULL';


-- creating a staging table

create table layoffs_staging as
select *
from layoffs
where 1 = 0;


-- checking the result

pragma table_info(layoffs_staging);


-- inserting data into the staging table 

insert into layoffs_staging 
select * 
from layoffs;


-- identifying the duplicate row with CTE and window function

with duplicate_cte as(
select *, 
ROW_NUMBER() over(
partition by company,
location,
industry,
total_laid_off,
percentage_laid_off,
layoff_date,
stage,
country,
funds_raised_millions) as rownum
from layoffs_staging
)
select * from duplicate_cte where rownum>1;


--checking the result 

select * 
from layoffs_staging 
where company 
like "Casper";


-- creating a second staging table 

create table 
layoffs_staging2 as 
select * 
from layoffs_staging 
where 1=0;


-- adding a row rownum for the count of duplicate rows

alter table layoffs_staging2 
add column rownum INTEGER;


-- insering into staging table

insert into layoffs_staging2
select *, 
ROW_NUMBER() over(
partition by company,
location,
industry,
total_laid_off,
percentage_laid_off,
layoff_date,
stage,
country,
funds_raised_millions) as rownum
from layoffs_staging;


-- selecting the duplicate rows

select * 
from layoffs_staging2 
where rownum>1;


-- deleting the duplicates

delete 
from layoffs_staging2 
where rownum>1;


-- trimming the company name

select 
distinct trim(company) 
from layoffs_staging2;


-- updating company name with trimmed name

update layoffs_staging2
set company = trim(company);


-- updating industry name with trimmed value 

update layoffs_staging2
set industry = trim(industry);


-- looking for same industry with distinct name 

select distinct industry 
from layoffs_staging2
order by industry;


-- crypto industry have multiple names 

select * 
from layoffs_staging2 
where industry 
like "Crypto%";


-- updating the crypto industry with name "Crypto"

update layoffs_staging2
set industry = "Crypto"
where industry like "Crypto%";


-- looking for same location names

select 
distinct location 
from layoffs_staging2 
order by 1;


-- looking for same country names

select 
distinct country 
from layoffs_staging2 
order by 1;


-- identified same country name 

select 
distinct country, 
trim(country,".") 
from layoffs_staging2 
where country 
like "United States%";


-- updating the country name

update layoffs_staging2
set country = trim(country,".")
where country 
like "United States%";


-- looking for null industry name

select * 
from layoffs_staging2
where industry is null 
or industry = "";


-- looking for industry names for null values

select 
t1.company,
t1.location,
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;


-- updating the null industry names with corresponding industry names

update layoffs_staging2
set industry = (
select t2.industry
from layoffs_staging2 t2
where t2.company = layoffs_staging2.company
and t2.industry is not null
limit 1
)
where industry is null
and company in (
select company
from layoffs_staging2
where industry is not null
);


-- looking for useless rows with has null values

select * 
from layoffs_staging2
where total_laid_off is null 
and percentage_laid_off is null;


-- deleting the useless rows

delete from 
layoffs_staging2
where total_laid_off is null 
and percentage_laid_off is null;


-- removing the rownum column from the table as it is has no more use

alter table layoffs_staging2
drop column rownum;


-- final table with is clean

select * 
from layoffs_staging2;