explolatory_data_analysis.sql

-- Explolatory data analis

SELECT *
FROM layoff_staging2;

SELECT MAX(total_laid_off),MAX(percentage_laid_off)
FROM layoff_staging2;
 SELECT *
FROM layoff_staging2
WHERE percentage_laid_off = 1
ORDER BY funds_raised_millions  DESC ;

SELECT company, SUM(total_laid_off)
FROM layoff_staging2
GROUP BY company
ORDER BY  2 DESC;
SELECT MIN(`date`),MAX(`date`)
FROM layoff_staging2;

SELECT country, SUM(total_laid_off)
FROM layoff_staging2
GROUP BY country
ORDER BY  2 DESC;

SELECT `date`, SUM(total_laid_off)
FROM layoff_staging2
GROUP BY `date`
ORDER BY  1 DESC;

SELECT YEAR(`date`), SUM(total_laid_off)
FROM layoff_staging2
GROUP BY YEAR(`date`)
ORDER BY  1 DESC;

SELECT stage, SUM(total_laid_off)
FROM layoff_staging2
GROUP BY stage
ORDER BY  2 DESC;

SELECT company, AVG(percentage_laid_off)
FROM layoff_staging2
GROUP BY company
ORDER BY  2 DESC;

SELECT *
FROM layoff_staging2;

SELECT SUBSTRING(`date`,1,7) AS `MONTH` , SUM(total_laid_off)
FROM layoff_staging2
WHERE SUBSTRING(`date`,1,7) IS NOT NULL
GROUP BY `MONTH`
ORDER BY 1 ASC;

WITH Rolling_Total AS
(
SELECT DATE_FORMAT(date, '%Y-%m') AS `MONTH` , SUM(total_laid_off) AS total_off
FROM layoff_staging2
WHERE date IS NOT NULL
GROUP BY `MONTH`

)
SELECT `MONTH`, total_off,
SUM(total_off) OVER(ORDER BY `MONTH`) AS rolling_total
FROM Rolling_Total;

SELECT company, SUM(total_laid_off)
FROM layoff_staging2
GROUP BY company
ORDER BY  2 DESC;

SELECT company,YEAR(`date`),SUM(total_laid_off)
FROM layoff_staging2
GROUP BY company,YEAR(`date`)
ORDER BY SUM(total_laid_off) DESC;

WITH Company_Year (company,years,total_laid_off) AS
(
SELECT company,YEAR(`date`),SUM(total_laid_off)
FROM layoff_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
;