Covid19 Data Code.txt

SELECT *
FROM  `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NOT NULL
and date < '2023-01-01'
ORDER BY 3, 4
;


-- Showing DATA to use

SELECT location, date, total_cases,new_cases, total_deaths, population
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NOT NULL
and date < '2023-01-01'
ORDER BY 1, 2
;
 
  
-- showing likelyhood of dying if you contract covid in your country using France as an example

SELECT location, date, total_cases, total_deaths, (total_deaths/total_cases)*100 AS deathpercentage
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE location LIKE '%France%'
  AND continent IS NOT NULL
  and date < '2023-01-01'
ORDER BY 1,2
;
  
-- showing percentage OF population with Covid using France as an example

SELECT location, date, total_cases, population, (total_cases/population)*100 AS percentagepopulationinfected
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE location LIKE '%France%'
  AND continent IS NOT NULL
  and date < '2023-01-01'
ORDER BY 1, 2
 ;


-- looking at countries with highest infection rate compared to population

SELECT location, population, MAX(total_cases) AS highest_infection_count,
  MAX((total_cases/population))*100 AS percentpopulationinfected
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NOT NULL and date < '2023-01-01'
GROUP BY location, population
ORDER BY percentpopulationinfected DESC 
;


-- looking at countries with highest infection rate compared to population and date

SELECT location, population, date, MAX(total_cases) AS highest_infection_count,
  MAX((total_cases/population))*100 AS percentpopulationinfected
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NOT NULL
and location not in ('world', 'European Union', 'Upper middle income', 'High income')
and date < '2023-01-01'
GROUP BY location, population, date
ORDER BY percentpopulationinfected DESC 
;


-- Showing highest death count per population

SELECT continent, MAX(total_deaths) AS total_deaths_count
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NOT NULL
and date < '2023-01-01'
GROUP BY continent
ORDER BY total_deaths_count DESC 
;


-- Global numbers

SELECT date, SUM(new_cases) AS total_cases, SUM(new_deaths) AS total_deaths,
  IFNULL(SUM(new_deaths)/ NULLIF(SUM(new_cases), 0), 0)*100 AS deathpercentage
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NULL
  AND date IS NOT NULL 
  and date < '2023-01-01'
GROUP BY date
ORDER BY 1, 2
;

-- Global numbers without Grouping by date

SELECT SUM(new_cases) AS total_cases, SUM(new_deaths) AS total_deaths,
  IFNULL(SUM(new_deaths)/ NULLIF(SUM(new_cases), 0), 0)*100 AS deathpercentage
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths`
WHERE continent IS NULL
  AND date IS NOT NULL
  and date < '2023-01-01' 
ORDER BY 1, 2
;


-- Taking a look at the Covid Vaccinations

SELECT *
FROM  portfolioproject-380519.CovidVaccinations.Covid_Vaccinations
WHERE continent IS NOT NULL
and date < '2023-01-01'
ORDER BY 3, 4
;


-- looking AT total population vs vaccinated

SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
  SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.location, dea.date) AS rollingpeoplevaccinated
   --,(rolingpeoplevaccinted/population) * 100
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths` AS dea
JOIN portfolioproject-380519.CovidVaccinations.Covid_Vaccinations AS vac
ON dea.location = vac.location
  AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
ORDER BY 2, 3
 ; 


-- USING CTE

WITH popvsvac 
AS (
  SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
    SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.location, dea.date) AS rollingpeoplevaccinated 
    --, (rolingpeoplevaccinted/population) * 100
  FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths` AS dea
  JOIN portfolioproject-380519.CovidVaccinations.Covid_Vaccinations AS vac
  ON dea.location = vac.location
    AND dea.date = vac.date
  WHERE dea.continent IS NOT NULL
   and dea.date < '2023-01-01'
  )

SELECT *, (rollingpeoplevaccinated)*100
FROM popvsvac 
  ;


  -- TEMP TABLE 
  
  CREATE TEMP TABLE percentpopulationvaccinated (continent string,
  location string, date datetime, population integer,
  new_vaccinations integer, rollingpeoplevaccinated integer
   ) 
   AS
SELECT dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations,
  SUM(vac.new_vaccinations) OVER (PARTITION BY dea.location ORDER BY dea.location, dea.date) AS rollingpeoplevaccinated 
  --,(rolingpeoplevaccinted/population) * 100
FROM `portfolioproject-380519.CovidDeaths.Covid_Deaths` AS dea
JOIN portfolioproject-380519.CovidVaccinations.Covid_Vaccinations AS vac
ON dea.location = vac.location
  AND dea.date = vac.date
WHERE dea.continent IS NOT NULL
and dea.date < '2023-01-01' 
;

SELECT *, (rollingpeoplevaccinated)*100
FROM percentpopulationvaccinated 
;