covid_19_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### COVID-19 Data Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "select distinct country\n",
    "from covid_19_modeled.Location\n",
    "where country like '%China%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "select cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from covid_19_modeled.Event e join covid_19_modeled.Location l\n",
    "on e.location_id = l.id\n",
    "and country in ('Mainland China', 'China') \n",
    "group by date\n",
    "order by date desc\n",
    "limit 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "select cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from covid_19_modeled.Event e join covid_19_modeled.Location l\n",
    "on e.location_id = l.id\n",
    "where country = 'Italy' \n",
    "group by date\n",
    "order by date desc\n",
    "limit 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "select distinct country\n",
    "from covid_19_modeled.Location\n",
    "where country like 'U%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "select cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from covid_19_modeled.Event e join covid_19_modeled.Location l\n",
    "on e.location_id = l.id\n",
    "where country = 'US' \n",
    "group by date\n",
    "order by date desc\n",
    "limit 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "select state, cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from covid_19_modeled.Event e join covid_19_modeled.Location l\n",
    "on e.location_id = l.id\n",
    "where country = 'US' and state in ('Texas', 'New York', 'Washington')\n",
    "group by state, date\n",
    "order by date desc, state\n",
    "limit 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "create or replace view covid_19_modeled.v_china_cases as\n",
    "select cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from `cs327e-sp2020.covid_19_modeled.Event` e join `cs327e-sp2020.covid_19_modeled.Location` l\n",
    "on e.location_id = l.id\n",
    "where country in ('Mainland China', 'China') \n",
    "group by date\n",
    "order by date desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "create or replace view covid_19_modeled.v_italy_cases as\n",
    "select cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from `cs327e-sp2020.covid_19_modeled.Event` e join `cs327e-sp2020.covid_19_modeled.Location` l\n",
    "on e.location_id = l.id\n",
    "where country = 'Italy' \n",
    "group by date\n",
    "order by date desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "create or replace view covid_19_modeled.v_us_cases as\n",
    "select cast(last_update as date) as date, sum(confirmed) as confirmed, sum(deaths) as deaths, sum(recovered) as recovered\n",
    "from `cs327e-sp2020.covid_19_modeled.Event` e join `cs327e-sp2020.covid_19_modeled.Location` l\n",
    "on e.location_id = l.id\n",
    "where country = 'US' \n",
    "group by date\n",
    "order by date desc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Q1: create a view for New York, Texas, Washington"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}