world-layoffs-data-cleaning-EDA-sql/EDA.sql at main · mrithip/world-layoffs-data-cleaning-EDA-sql · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
-- EXPLORATORY DATA ANALYSIS

-- selecting the table

select *
from layoffs_staging2;


-- max layoffs

select
max(total_laid_off)
from layoffs_staging2;


-- max percentage of layoffs

select
max(percentage_laid_off)
from layoffs_staging2;


-- data of company with 100 percent layoffs

select *
from layoffs_staging2
where percentage_laid_off = 1
order by total_laid_off desc;


-- data of company with 100 percent layoff and highest funding

select *
from layoffs_staging2
where percentage_laid_off = 1
order by funds_raised_millions desc;


-- company with highest layoffs

select
company,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by company
order by 2 desc;


-- company with highest funding

select
company,
sum(funds_raised_millions) as totalfunds
from layoffs_staging2
group by company
order by 2 desc;


-- starting and ending date of layoffs

select
min(layoff_date) as startingdate,
max(layoff_date) as endingdate
from layoffs_staging2;


-- indestry with highest layoffs

select
industry,sum(total_laid_off) as totallayoff
from layoffs_staging2
group by industry
order by 2 desc;


-- country with highest layoffs

select
country,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by country
order by 2 desc;


-- years and total layoffs

select
substr(layoff_date,1,4) as year,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by year
order by 2 desc;


-- stages with highest layoffs

select
stage,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by stage
order by 2 desc;


-- month and its total layoffs

select
substr(layoff_date,6,2) as month,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by month
order by 2 desc;


-- rolling total layoffs with CTE and window functions

select
substr(layoff_date,1,7) as month,
sum(total_laid_off) as totallayoff
from layoffs_staging2
where month is not null
group by month
order by 1;


with rollingtotalcte as (
select
substr(layoff_date,1,7) as month,
sum(total_laid_off) as totallayoff
from layoffs_staging2
where month is not null
group by month
order by 1
)
select
month,
totallayoff,
sum(totallayoff) over(order by month) as rollingtotalcte
from rollingtotalcte;


-- highest 5 total rolling total layoffs of company in a year

select
company,
substr(layoff_date,1,4) as year,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by company,year
order by 3 desc;


with companyyearrankcte (company, year, totallayoff) as (
select
company,
substr(layoff_date,1,4) as year,
sum(total_laid_off) as totallayoff
from layoffs_staging2
group by company,year
),companyyear  as(
select *,DENSE_RANK() over(partition by year order by totallayoff desc) as ranking
from companyyearrankcte
where year is not null
)
select *
from companyyear
where ranking<=5;