<jupyter_start><jupyter_text>Before you begin1.   Use the [Cloud Resource Manager](https://console.cloud.google.com/cloud-resource-manager) to Create a Cloud Platform project if you do not already have one.2.   [Enable billing](https://support.google.com/cloud/answer/6293499enable-billing) for the project.3.   [Enable BigQuery](https://console.cloud.google.com/flows/enableapi?apiid=bigquery) APIs for the project.  Provide your credentials to the runtime<jupyter_code># If running from cloud-shell, DEVSHELL_PROJECT_ID environment variable may contain project ID.
# import os
# project_id = os.getenv("DEVSHELL_PROJECT_ID")

# Otherwise, set project_id manually
# project_id = 'XXXyourprojectID'
from google.colab import auth
auth.authenticate_user()
print('Authenticated')
project_id = 'XXX-your-project-id'<jupyter_output><empty_output><jupyter_text>GSP 787
https://www.qwiklabs.com/focuses/11988?parent=catalog

  Query 1: Cumulative confirmed covid-19 cases on April 15th, 2020.<jupyter_code>#%load_ext google.cloud.bigquery
#%%bigquery

from google.cloud import bigquery
client = bigquery.Client(project=project_id)
#sql = """
#SELECT
# source_year AS year,
# COUNT(is_male) AS birth_count
#FROM `bigquery-public-data.samples.natality`
#GROUP BY year
#ORDER BY year DESC
#LIMIT 15
#"""

sql = """
SELECT 
  SUM(cumulative_confirmed) AS total_cases_worldwide
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
  date = "2020-04-15"
"""

df = client.query(sql).to_dataframe()
df.head()<jupyter_output><empty_output><jupyter_text>Query 2: Worst affected areas<jupyter_code>from google.colab import syntax
import pandas as pd

query = syntax.sql('''
SELECT
    COUNT(*) AS count_of_states
FROM (
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='2020-04-10'
  AND subregion1_name IS NOT NULL
GROUP BY
  subregion1_name
)
WHERE death_count > 100
''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 3: Identifying hot spots<jupyter_code>query = syntax.sql('''
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='2020-04-10'
  AND subregion1_name is NOT NULL
GROUP BY
  subregion1_name
ORDER BY
  total_confirmed_cases DESC
)
WHERE
  total_confirmed_cases > 1000

''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 4: Fatality Ratio<jupyter_code>query = syntax.sql('''
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 "2020-04-01" AND "2020-04-30"
''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 5: Identifying specific day<jupyter_code>query = syntax.sql('''
SELECT
 date
FROM
  `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE
 country_name = 'Italy'
 AND cumulative_deceased > 10000
ORDER BY date
LIMIT 1
''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 6: Finding days with zero net new cases<jupyter_code>query = syntax.sql('''
WITH us_cases_by_date AS (
  SELECT
    date,
    SUM(cumulative_confirmed) AS cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="United States of America"
    AND date between '2020-02-21' and '2020-03-15'
  GROUP BY
    date
  ORDER BY
    date ASC
 )

, us_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 us_cases_by_date
)
SELECT
  COUNT(date)
FROM
  us_previous_day_comparison
WHERE
  net_new_cases = 0
''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 7: Doubling rate<jupyter_code>query = syntax.sql('''
WITH us_cases_by_date AS (
  SELECT
    date,
    SUM( cumulative_confirmed ) AS cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    country_name="United States of America"
    AND date between '2020-03-22' and '2020-04-20'
  GROUP BY
    date
  ORDER BY
    date ASC
 )

, us_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,
  (cases - LAG(cases) OVER(ORDER BY date))*100/LAG(cases) OVER(ORDER BY date) AS percentage_increase
FROM us_cases_by_date
)
SELECT
  Date,
  cases AS Confirmed_Cases_On_Day,
  previous_day AS Confirmed_Cases_Previous_Day,
  percentage_increase AS Percentage_Increase_In_Cases
FROM
  us_previous_day_comparison
WHERE
  percentage_increase > 10
''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 8: Recovery rate<jupyter_code>query = syntax.sql('''
WITH cases_by_country AS (
  SELECT
    country_name AS country,
    SUM(cumulative_confirmed) AS cases,
    SUM(cumulative_recovered) AS recovered_cases
  FROM
    `bigquery-public-data.covid19_open_data.covid19_open_data`
  WHERE
    date="2020-05-10"
  GROUP BY
    country_name
)

, recovered_rate AS (
  SELECT
    country, cases, recovered_cases,
    (recovered_cases * 100)/cases AS recovery_rate
  FROM
    cases_by_country
)

SELECT country, cases AS confirmed_cases, recovered_cases, recovery_rate
FROM
   recovered_rate
WHERE
   cases > 50000
ORDER BY recovery_rate DESC
LIMIT 10

''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Query 9: CDGR - Cumulative Daily Growth Rate<jupyter_code>query = syntax.sql('''
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-05-10')
  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
LIMIT 1
)

select first_day_cases, last_day_cases, days_diff, POWER(last_day_cases/first_day_cases,1/days_diff)-1 as cdgr
from summary
''')
pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')<jupyter_output><empty_output><jupyter_text>Create a Datastudio report<jupyter_code>query = syntax.sql('''
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-15'
  AND '2020-04-30'
  AND country_name='United States of America'
GROUP BY date
''')
df = pd.io.gbq.read_gbq(query, project_id=project_id, dialect='standard')

ax = df.plot(kind='bar',x='date',y='country_deaths',figsize=(15,7))
ax.set_title('deaths')
ax.set_xlabel('date')
ax.set_ylabel('deaths')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09988340458608629, 'fraction_numerical': 0.03018525715766291, 'mean_word_length': 4.6932153392330385, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 50, 'https://': 4, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '570813', 'license': 'Unlicense', 'n_tokens_mistral': 3124, 'n_tokens_neox': 2798, 'n_words': 563, 'path': 'examples/Data-Engineering/bigquery/gsp787-insights-from-bq/gsp787.ipynb', 'repo_name': 'gtoro29/gcp', 'size': '7719'}