GSP787

solution.sql

SELECT sum(cumulative_confirmed) as total_cases_worldwide
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date=?
;

WITH
  deaths_by_states AS (
  SELECT
    subregion1_name AS state,
    SUM(cumulative_deceased) AS death_count
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="United States of America"
    AND date=?
    AND subregion1_name IS NOT NULL
  GROUP BY
    subregion1_name )
SELECT
  COUNT(*) AS count_of_states
FROM
  deaths_by_states
WHERE
  death_count > ?
;

SELECT
  *
FROM (
  SELECT
    subregion1_name AS state,
    SUM(cumulative_confirmed) AS total_confirmed_cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_code="US"
    AND date=?
    AND subregion1_name IS NOT NULL
  GROUP BY
    subregion1_name
  ORDER BY
    total_confirmed_cases DESC )
WHERE
  total_confirmed_cases > ?
;

SELECT
  SUM(cumulative_confirmed) AS total_confirmed_cases,
  SUM(cumulative_deceased) AS total_deaths,
  (SUM(cumulative_deceased)/SUM(cumulative_confirmed))*100 AS case_fatality_ratio
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_name="Italy"
  AND date BETWEEN ? AND ?
;

SELECT
  date
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  country_name="Italy"
  AND cumulative_deceased>?
ORDER BY
  date ASC
LIMIT
  1
;

WITH
  india_cases_by_date AS (
  SELECT
    date,
    SUM( cumulative_confirmed ) AS cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name ="India"
    AND date BETWEEN ? AND ?
  GROUP BY
    date
  ORDER BY
    date ASC ),
  india_previous_day_comparison AS (
  SELECT
    date,
    cases,
    LAG(cases) OVER(ORDER BY date) AS previous_day,
    cases - LAG(cases) OVER(ORDER BY date) AS net_new_cases
  FROM
    india_cases_by_date )
SELECT
  COUNT(*)
FROM
  india_previous_day_comparison
WHERE
  net_new_cases=0
;

WITH
  france_cases AS (
  SELECT
    date,
    SUM(cumulative_confirmed) AS total_cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="France"
    AND date IN ('2020-01-24', '2020-04-15')
  GROUP BY
    date
  ORDER BY
    date),
  summary AS (
  SELECT
    MIN_BY(total_cases, date) AS first_day_cases,
    MAX_BY(total_cases, date) AS last_day_cases,
    DATE_DIFF(
      MAX(date),
      MIN(date),
      day) AS days_diff
  FROM
    france_cases
   )
SELECT
  first_day_cases,
  last_day_cases,
  days_diff,
  (POW((last_day_cases/first_day_cases),(1/days_diff))-1) AS cdgr
FROM
  summary
;

SELECT
  date,
  SUM(cumulative_confirmed) AS country_cases,
  SUM(cumulative_deceased) AS country_deaths
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  date BETWEEN '2020-03-31' AND '2020-04-17'
  AND country_name ="United States of America"
GROUP BY
  date
;

task 9

by chance

WITH
  france_cases AS (
  SELECT
    date,
    SUM(cumulative_confirmed) AS total_cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="France"
    AND date IN ('2020-01-24',
      '2020-04-15')
  GROUP BY
    date
  ORDER BY
    date),
  summary AS (
  SELECT
    total_cases AS first_day_cases,
    LEAD(total_cases) OVER(ORDER BY date) AS last_day_cases,
    DATE_DIFF(LEAD(date) OVER(ORDER BY date),date, day) AS days_diff
  FROM
    france_cases
   )
SELECT
  first_day_cases,
  last_day_cases,
  days_diff,
  POW((last_day_cases/first_day_cases),(1/days_diff))-1 AS cdgr
FROM
  summary
  LIMIT 1
  -- OFFSET 1

ref. https://github.com/hiiruki/google-cloudskillsboost/blob/main/challenge-labs/GSP787/index.md