Portfolio.Projects/18.Exploratory.DataAnalysis.sql at main · AndreTeixeira08/Portfolio.Projects · 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
-- Exploratory Data Analysis

select *
from layoffs_staging2;


select max(total_laid_off), max(percentage_laid_off)
from layoffs_staging2;

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

Select company, sum(total_laid_off)
from layoffs_staging2
group by company
order by 2 desc;

select min(`date`), max(`date`)
from layoffs_staging2;

Select country, sum(total_laid_off)
from layoffs_staging2
group by country
order by 2 desc;

select *
from layoffs_staging2;

Select year(`date`), sum(total_laid_off)
from layoffs_staging2
group by year(`date`)
order by 1 desc;

Select stage, sum(total_laid_off)
from layoffs_staging2
group by stage
order by 2 desc;

Select company, avg(percentage_laid_off)
from layoffs_staging2
group by company
order by 2 desc;


Select substring(`date`,1,7) as `month`, sum(total_laid_off)
from layoffs_staging2
where substring(`date`,1,7) is not null
group by `month`
order by 1 asc
;

With rolling_total as
(
Select substring(`date`,1,7) as `month`, sum(total_laid_off) as total_off
from layoffs_staging2
where substring(`date`,1,7) is not null
group by `month`
order by 1 asc
)
select `month`, total_off
,sum(total_off) over(order by `month`) as rolling_total
from rolling_total;

Select company, sum(total_laid_off)
from layoffs_staging2
group by company
order by 2 desc;

Select company, year(`date`), sum(total_laid_off)
from layoffs_staging2
group by company, year(`date`)
Order by company asc
;

Select company, year(`date`), sum(total_laid_off)
from layoffs_staging2
group by company, year(`date`)
Order by 3 desc
;

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`)
Order by 3 desc
), 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
;