 milestone4.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "raising-framing",
   "metadata": {},
   "source": [
    "### Run pipeline to remove duplicates from table locations and standardize US state names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "beneficial-registrar",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "        if (typeof window.interactive_beam_jquery == 'undefined') {\n",
       "          var jqueryScript = document.createElement('script');\n",
       "          jqueryScript.src = 'https://code.jquery.com/jquery-3.4.1.slim.min.js';\n",
       "          jqueryScript.type = 'text/javascript';\n",
       "          jqueryScript.onload = function() {\n",
       "            var datatableScript = document.createElement('script');\n",
       "            datatableScript.src = 'https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js';\n",
       "            datatableScript.type = 'text/javascript';\n",
       "            datatableScript.onload = function() {\n",
       "              window.interactive_beam_jquery = jQuery.noConflict(true);\n",
       "              window.interactive_beam_jquery(document).ready(function($){\n",
       "                \n",
       "              });\n",
       "            }\n",
       "            document.head.appendChild(datatableScript);\n",
       "          };\n",
       "          document.head.appendChild(jqueryScript);\n",
       "        } else {\n",
       "          window.interactive_beam_jquery(document).ready(function($){\n",
       "            \n",
       "          });\n",
       "        }"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jupyter/venv/lib/python3.7/site-packages/apache_beam/io/gcp/bigquery.py:1936: BeamDeprecationWarning: options is deprecated since First stable release. References to <pipeline>.options will not be supported\n",
      "  temp_location = pcoll.pipeline.options.view_as(\n",
      "/home/jupyter/venv/lib/python3.7/site-packages/apache_beam/io/gcp/bigquery_file_loads.py:956: BeamDeprecationWarning: options is deprecated since First stable release. References to <pipeline>.options will not be supported\n",
      "  temp_location = p.options.view_as(GoogleCloudOptions).temp_location\n"
     ]
    }
   ],
   "source": [
    "%run locations_dataflow.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "visible-renaissance",
   "metadata": {},
   "source": [
    "#### Check that only duplicates were removed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "vulnerable-spyware",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_locations</th>\n",
       "      <th>locations_Dataflow_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4305</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_locations  locations_Dataflow_count\n",
       "0                4305                      4305"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*)+(SELECT count(*)\n",
    "                FROM datamart.locations\n",
    "                WHERE latitude = 0 or longitude =0)-1+(SELECT count(*)\n",
    "                                                        FROM datamart.locations\n",
    "                                                        WHERE latitude IS null or longitude IS null)-1 AS distinct_locations,\n",
    "                (SELECT count(*)\n",
    "                FROM datamart.locations_Dataflow) AS locations_Dataflow_count\n",
    "FROM (SELECT DISTINCT  latitude, longitude FROM datamart.locations)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "urban-mountain",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "resident-punch",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_PK</th>\n",
       "      <th>locations_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4305</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  locations_count\n",
       "0         4305             4305"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.locations_Dataflow) AS locations_count\n",
    "FROM (\n",
    "    SELECT DISTINCT id\n",
    "    FROM datamart.locations_Dataflow\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "global-vermont",
   "metadata": {},
   "source": [
    "### SQL Transforms on locations_Dataflow"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "improved-ontario",
   "metadata": {},
   "source": [
    "#### Ensure that abbreviated US state names have all been standardized to the full name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "interim-toilet",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Washington</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Wuhan Evacuee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>69 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  count\n",
       "0             None      1\n",
       "1          Alabama     71\n",
       "2           Alaska     37\n",
       "3   American Samoa      1\n",
       "4          Arizona     22\n",
       "..             ...    ...\n",
       "64      Washington     53\n",
       "65   West Virginia     57\n",
       "66       Wisconsin     76\n",
       "67   Wuhan Evacuee      1\n",
       "68         Wyoming     27\n",
       "\n",
       "[69 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT distinct state, count(*) AS count\n",
    "FROM datamart.locations_Dataflow\n",
    "WHERE country='US'\n",
    "GROUP BY state\n",
    "ORDER BY state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "surgical-corpus",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Dataflow\n",
    "SET state='District of Columbia'\n",
    "WHERE state='D.C.'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "african-proceeding",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Dataflow\n",
    "SET state='Texas', combined_key='Diamond Princess, Lackland, Texas, US'\n",
    "WHERE state='TX (From Diamond Princess)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "brave-audio",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Dataflow\n",
    "SET state='Nebraska', combined_key='Diamond Princess, Omaha, Nebraska, US'\n",
    "WHERE state='NE (From Diamond Princess)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "statewide-registrar",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.locations_Dataflow\n",
    "SET state='California', combined_key='Diamond Princess, Travis, California, US'\n",
    "WHERE state='CA (From Diamond Princess)'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "social-bidder",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>American Samoa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Washington</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Wuhan Evacuee</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>65 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  count\n",
       "0             None      1\n",
       "1          Alabama     71\n",
       "2           Alaska     37\n",
       "3   American Samoa      1\n",
       "4          Arizona     22\n",
       "..             ...    ...\n",
       "60      Washington     53\n",
       "61   West Virginia     57\n",
       "62       Wisconsin     76\n",
       "63   Wuhan Evacuee      1\n",
       "64         Wyoming     27\n",
       "\n",
       "[65 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT distinct state, count(*) AS count\n",
    "FROM datamart.locations_Dataflow\n",
    "WHERE country='US'\n",
    "GROUP BY state\n",
    "ORDER BY state"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "tested-bouquet",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "annual-affiliate",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_PK</th>\n",
       "      <th>locations_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4305</td>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  locations_count\n",
       "0         4305             4305"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.locations_Dataflow) AS locations_count\n",
    "FROM (\n",
    "    SELECT DISTINCT id\n",
    "    FROM datamart.locations_Dataflow\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "intimate-oliver",
   "metadata": {},
   "source": [
    "### SQL Transforms on cases (milestone 2's version)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "proper-sarah",
   "metadata": {},
   "source": [
    "### Remake milestone 2's cases table since it was modified in milestone 3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "republican-westminster",
   "metadata": {},
   "source": [
    "#### create preliminary covid table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "logical-fever",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.covid AS\n",
    "SELECT null AS id, fips, admin2, province_state, country_region AS Country, last_update, latitude, longitude, confirmed, deaths, recovered, active, combined_key, incident_rate, case_fatality_ratio\n",
    "FROM jhu_daily_reports_staging.covid_winter2020\n",
    "UNION ALL\n",
    "SELECT null AS id, fips, admin2, province_state, country_region AS Country, last_update, latitude, longitude, confirmed, deaths, recovered, active, combined_key, incident_rate, case_fatality_ratio\n",
    "FROM jhu_daily_reports_staging.covid_spring2020\n",
    "UNION ALL\n",
    "SELECT null AS id, fips, admin2, province_state, country_region AS Country, CAST(last_update AS STRING) AS last_update, lat AS latitude, long_ AS longitude, confirmed, deaths, recovered, active, combined_key, incidence_rate AS incident_rate, case_fatality_ratio\n",
    "FROM jhu_daily_reports_staging.covid_summer2020\n",
    "UNION ALL\n",
    "SELECT null AS id, fips, admin2, province_state, country_region AS Country, CAST(last_update AS STRING) AS last_update, lat AS latitude, long_ AS longitude, confirmed, deaths, recovered, active, combined_key, incident_rate, case_fatality_ratio\n",
    "FROM jhu_daily_reports_staging.covid_fall2020\n",
    "UNION ALL\n",
    "SELECT null AS id, fips, admin2, province_state, country_region AS Country, CAST(last_update AS STRING) AS last_update, lat AS latitude, long_ AS longitude, confirmed, deaths, recovered, active, combined_key, incident_rate, case_fatality_ratio\n",
    "FROM jhu_daily_reports_staging.covid_winter2021\n",
    "UNION ALL\n",
    "SELECT null AS id, fips, admin2, province_state, country_region AS Country, CAST(last_update AS STRING) AS last_update, lat AS latitude, long_ AS longitude, confirmed, deaths, recovered, active, combined_key, incident_rate, case_fatality_ratio\n",
    "FROM jhu_daily_reports_staging.covid_spring2021"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "unusual-reply",
   "metadata": {},
   "source": [
    "#### compute fingerprint ids for preliminary covid table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "perceived-pearl",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.covid\n",
    "SET id=FARM_FINGERPRINT(country)\n",
    "WHERE province_state IS null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "funded-edinburgh",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.covid\n",
    "SET id=FARM_FINGERPRINT(concat(province_state, country))\n",
    "WHERE (province_state IS NOT null AND country!='US')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cultural-cliff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.covid\n",
    "SET id=FARM_FINGERPRINT(concat(admin2, province_state, country))\n",
    "WHERE (province_state IS NOT null AND country='US' AND admin2 IS NOT null AND fips IS NULL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "similar-uncle",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.covid\n",
    "SET id=FARM_FINGERPRINT(concat(CAST(fips AS STRING), admin2, province_state, country))\n",
    "WHERE (province_state IS NOT null AND country='US' AND admin2 IS NOT null AND fips IS NOT null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "unlikely-undergraduate",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.covid\n",
    "SET id=FARM_FINGERPRINT(concat(CAST(fips AS STRING), province_state, country))\n",
    "WHERE (province_state IS NOT null AND country='US' AND admin2 IS null AND fips IS NOT null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "suited-planner",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.covid\n",
    "SET id=FARM_FINGERPRINT(concat(province_state, country))\n",
    "WHERE (province_state IS NOT null AND country='US' AND admin2 IS null AND fips IS null)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "wrong-gathering",
   "metadata": {},
   "source": [
    "#### check that there is no null id field in the preliminary covid table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "plain-setup",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>null_id_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   null_id_count\n",
       "0              0"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS null_id_count\n",
    "FROM datamart.covid\n",
    "WHERE id IS null"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "grand-scheduling",
   "metadata": {},
   "source": [
    "#### create Cases_raw table from preliminary covid table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "extended-western",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.Cases_raw AS\n",
    "SELECT id AS location_id, last_update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM datamart.covid"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "linear-monkey",
   "metadata": {},
   "source": [
    "#### create Cases_hypen_date_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "informal-breed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.Cases_hyphen_date_fixed AS\n",
    "SELECT location_id, CAST(last_update AS DATETIME) AS last_update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM datamart.Cases_raw\n",
    "WHERE strpos(last_update, '-') > 0 AND strpos(last_update, ' ') > 0 AND strpos(last_update, '+') = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "inside-stockholm",
   "metadata": {},
   "source": [
    "#### create Cases_plus_date_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "light-eligibility",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.Cases_plus_date_fixed AS\n",
    "SELECT location_id,CAST(CAST(last_update AS TIMESTAMP) AS DATETIME) AS last_update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM datamart.Cases_raw\n",
    "WHERE strpos(last_update, '-') > 0 AND strpos(last_update, ' ') > 0 AND strpos(last_update, '+') > 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "emerging-skirt",
   "metadata": {},
   "source": [
    "#### create Cases_slash_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "acceptable-letters",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.Cases_slash_date AS\n",
    "SELECT * \n",
    "FROM datamart.Cases_raw\n",
    "WHERE strpos(last_update, '/') > 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "continent-saturn",
   "metadata": {},
   "source": [
    "#### create Cases_slash_date_fixed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "pleasant-polymer",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.Cases_slash_date_fixed AS\n",
    "(SELECT location_id, PARSE_DATETIME('%m/%d/%y %H:%M', last_update) AS Last_Update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM datamart.Cases_slash_date \n",
    "WHERE length(split(split(last_update, ' ')[offset(0)], '/')[offset(2)]) = 2\n",
    "UNION ALL\n",
    "SELECT location_id, PARSE_DATETIME('%m/%d/%Y %H:%M', last_update) AS Last_Update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM datamart.Cases_slash_date \n",
    "WHERE length(split(split(last_update, ' ')[offset(0)], '/')[offset(2)]) = 4\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "attempted-ghana",
   "metadata": {},
   "source": [
    "#### create cases table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "chubby-fiber",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.cases AS\n",
    "(SELECT * \n",
    "FROM datamart.Cases_hyphen_date_fixed\n",
    "UNION ALL\n",
    "SELECT *\n",
    "FROM datamart.Cases_plus_date_fixed\n",
    "UNION ALL\n",
    "SELECT *\n",
    "FROM datamart.Cases_slash_date_fixed\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "copyrighted-freeze",
   "metadata": {},
   "source": [
    "#### remove duplicate cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "communist-pride",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.cases AS\n",
    "SELECT DISTINCT *\n",
    "FROM datamart.cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "excited-memorial",
   "metadata": {},
   "source": [
    "#### check if there a still duplicate cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "secure-wisdom",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>location_id</th>\n",
       "      <th>last_update</th>\n",
       "      <th>duplicate_cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3183797238637395852</td>\n",
       "      <td>2021-04-02 15:13:53</td>\n",
       "      <td>146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-1181716864917925688</td>\n",
       "      <td>2021-04-02 15:13:53</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2231787937947570706</td>\n",
       "      <td>2020-12-21 13:27:30</td>\n",
       "      <td>92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8778414404485170876</td>\n",
       "      <td>2020-03-17 11:53:10</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8778414404485170876</td>\n",
       "      <td>2020-02-24 23:33:02</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5908437069604758697</td>\n",
       "      <td>2020-03-14 11:53:29</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8009782315755301025</td>\n",
       "      <td>2020-08-04 02:27:56</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-7786566325262141720</td>\n",
       "      <td>2020-12-21 13:27:30</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>7430223413705102985</td>\n",
       "      <td>2020-03-14 20:13:16</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>7612477503619687113</td>\n",
       "      <td>2020-01-31 10:37:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           location_id         last_update  duplicate_cases\n",
       "0  3183797238637395852 2021-04-02 15:13:53              146\n",
       "1 -1181716864917925688 2021-04-02 15:13:53              111\n",
       "2  2231787937947570706 2020-12-21 13:27:30               92\n",
       "3  8778414404485170876 2020-03-17 11:53:10                3\n",
       "4  8778414404485170876 2020-02-24 23:33:02                3\n",
       "5  5908437069604758697 2020-03-14 11:53:29                2\n",
       "6  8009782315755301025 2020-08-04 02:27:56                2\n",
       "7 -7786566325262141720 2020-12-21 13:27:30                2\n",
       "8  7430223413705102985 2020-03-14 20:13:16                2\n",
       "9  7612477503619687113 2020-01-31 10:37:00                2"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT location_id, last_update, count(*) AS duplicate_cases\n",
    "FROM datamart.cases\n",
    "GROUP BY location_id, last_update\n",
    "HAVING count(*) > 1\n",
    "ORDER BY count(*) DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "heated-rebound",
   "metadata": {},
   "source": [
    "#### remove duplicates based on confirmed, deaths, and recovered cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "environmental-business",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.cases AS\n",
    "SELECT location_id, last_update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM(SELECT *, rank() over (partition by location_id, last_update order by confirmed DESC, deaths DESC, recovered DESC ) as rank\n",
    "FROM datamart.cases)\n",
    "WHERE rank = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "accessory-banner",
   "metadata": {},
   "source": [
    "#### PK check: if same number then no duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "cognitive-breakdown",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_PK</th>\n",
       "      <th>cases_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1475767</td>\n",
       "      <td>1475767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  cases_count\n",
       "0      1475767      1475767"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.cases) AS cases_count\n",
    "FROM (\n",
    "    SELECT DISTINCT location_id, last_update\n",
    "    FROM datamart.cases\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "checked-costume",
   "metadata": {},
   "source": [
    "#### FK check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "divine-metabolism",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>foreign_key_violations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   foreign_key_violations\n",
       "0                       0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS foreign_key_violations\n",
    "FROM datamart.cases AS c\n",
    "LEFT JOIN datamart.locations AS l ON c.location_id=l.id\n",
    "WHERE l.id IS null"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dirty-layout",
   "metadata": {},
   "source": [
    "#### make a copy of the milestone 2 cases table to modify: new_cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "appreciated-basics",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.new_cases AS\n",
    "SELECT * \n",
    "FROM datamart.cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "final-scanner",
   "metadata": {},
   "source": [
    "### SQL Transforms: Update location_id of the orphaned records in table new_cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bright-tanzania",
   "metadata": {},
   "source": [
    "#### get the abandoned location records {review!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "surprising-furniture",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.abandoned_locations AS\n",
    "SELECT *\n",
    "FROM datamart.locations AS l\n",
    "WHERE l.id NOT IN (SELECT d.id FROM datamart.locations_Dataflow AS d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "alive-power",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## don't run, may not need\n",
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.abandoned_locations AS\n",
    "SELECT a.location_id AS abandoned_id, l.latitude, l.longitude\n",
    "FROM datamart.abandoned_locations AS a\n",
    "JOIN datamart.locations AS l ON l.id=a.location_id"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "clear-seminar",
   "metadata": {},
   "source": [
    "#### Create a junction table to match abandoned ids to kept ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "infinite-singles",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>kept_id</th>\n",
       "      <th>rid_id</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-8869782645955986065</td>\n",
       "      <td>-3845568702501865415</td>\n",
       "      <td>46.230401</td>\n",
       "      <td>-118.477554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-8673092529043857217</td>\n",
       "      <td>6005352482531411353</td>\n",
       "      <td>-51.796300</td>\n",
       "      <td>-59.523600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-8054612975524920966</td>\n",
       "      <td>8624219087402172771</td>\n",
       "      <td>31.825700</td>\n",
       "      <td>117.226400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-6976893411093504258</td>\n",
       "      <td>-4147227804562828434</td>\n",
       "      <td>13.443200</td>\n",
       "      <td>-15.310100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-6843305877291741994</td>\n",
       "      <td>-1909288589590523291</td>\n",
       "      <td>36.140800</td>\n",
       "      <td>-5.353600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-6369225154884557074</td>\n",
       "      <td>24401797468677984</td>\n",
       "      <td>27.614000</td>\n",
       "      <td>115.722100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-6190303266847582575</td>\n",
       "      <td>-2262583433077819139</td>\n",
       "      <td>40.767273</td>\n",
       "      <td>-73.971526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-6125909817942919488</td>\n",
       "      <td>-8744039356309243705</td>\n",
       "      <td>15.097900</td>\n",
       "      <td>145.673900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>-6125909817942919488</td>\n",
       "      <td>3620381022065474036</td>\n",
       "      <td>15.097900</td>\n",
       "      <td>145.673900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-5555958697111529338</td>\n",
       "      <td>5491571796867280413</td>\n",
       "      <td>40.712800</td>\n",
       "      <td>-74.006000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               kept_id               rid_id   latitude   longitude\n",
       "0 -8869782645955986065 -3845568702501865415  46.230401 -118.477554\n",
       "1 -8673092529043857217  6005352482531411353 -51.796300  -59.523600\n",
       "2 -8054612975524920966  8624219087402172771  31.825700  117.226400\n",
       "3 -6976893411093504258 -4147227804562828434  13.443200  -15.310100\n",
       "4 -6843305877291741994 -1909288589590523291  36.140800   -5.353600\n",
       "5 -6369225154884557074    24401797468677984  27.614000  115.722100\n",
       "6 -6190303266847582575 -2262583433077819139  40.767273  -73.971526\n",
       "7 -6125909817942919488 -8744039356309243705  15.097900  145.673900\n",
       "8 -6125909817942919488  3620381022065474036  15.097900  145.673900\n",
       "9 -5555958697111529338  5491571796867280413  40.712800  -74.006000"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT l.id AS kept_id, a.id AS rid_id, l.latitude, l.longitude\n",
    "FROM datamart.locations_Dataflow AS l\n",
    "JOIN datamart.abandoned_locations AS a \n",
    "ON l.latitude=a.latitude AND l.longitude=a.longitude\n",
    "ORDER BY kept_id, rid_id\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "difficult-configuration",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.junction AS\n",
    "SELECT l.id AS kept_id, a.id AS rid_id, l.latitude, l.longitude\n",
    "FROM datamart.locations_Dataflow AS l\n",
    "JOIN datamart.abandoned_locations AS a \n",
    "ON l.latitude=a.latitude AND l.longitude=a.longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "unsigned-biotechnology",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "UPDATE datamart.new_cases AS c\n",
    "SET c.location_id=(SELECT j.kept_id FROM datamart.junction AS j WHERE c.location_id=j.rid_id)\n",
    "WHERE c.location_id IN (SELECT ju.rid_id FROM datamart.junction AS ju)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "latin-check",
   "metadata": {},
   "source": [
    "#### PK check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "chemical-landscape",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_PK</th>\n",
       "      <th>cases_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1475736</td>\n",
       "      <td>1475767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  cases_count\n",
       "0      1475736      1475767"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.new_cases) AS cases_count\n",
    "FROM (\n",
    "    SELECT DISTINCT location_id, last_update\n",
    "    FROM datamart.new_cases\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "executed-plastic",
   "metadata": {},
   "source": [
    "#### remove duplicates based on confirmed, deaths, and recovered cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "elder-survivor",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.new_cases AS\n",
    "SELECT location_id, last_update, confirmed, deaths, recovered, active, incident_rate, case_fatality_ratio\n",
    "FROM(SELECT *, rank() over (partition by location_id, last_update order by confirmed DESC, deaths DESC, recovered DESC ) as rank\n",
    "FROM datamart.new_cases)\n",
    "WHERE rank = 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "obvious-conclusion",
   "metadata": {},
   "source": [
    "#### PK re-check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "electric-employee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_PK</th>\n",
       "      <th>cases_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1475736</td>\n",
       "      <td>1475737</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  cases_count\n",
       "0      1475736      1475737"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.new_cases) AS cases_count\n",
    "FROM (\n",
    "    SELECT DISTINCT location_id, last_update\n",
    "    FROM datamart.new_cases\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "vanilla-michael",
   "metadata": {},
   "source": [
    "##### investigate duplicate case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "exterior-smart",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>location_id</th>\n",
       "      <th>last_update</th>\n",
       "      <th>duplicate_cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-278257842025680577</td>\n",
       "      <td>2020-03-08 17:33:03</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          location_id         last_update  duplicate_cases\n",
       "0 -278257842025680577 2020-03-08 17:33:03                2"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT location_id, last_update, count(*) AS duplicate_cases\n",
    "FROM datamart.new_cases\n",
    "GROUP BY location_id, last_update\n",
    "HAVING count(*) > 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "collective-gauge",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>location_id</th>\n",
       "      <th>last_update</th>\n",
       "      <th>confirmed</th>\n",
       "      <th>deaths</th>\n",
       "      <th>recovered</th>\n",
       "      <th>active</th>\n",
       "      <th>incident_rate</th>\n",
       "      <th>case_fatality_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-278257842025680577</td>\n",
       "      <td>2020-03-08 17:33:03</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-278257842025680577</td>\n",
       "      <td>2020-03-08 17:33:03</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          location_id         last_update  confirmed  deaths  recovered  \\\n",
       "0 -278257842025680577 2020-03-08 17:33:03          6       0          0   \n",
       "1 -278257842025680577 2020-03-08 17:33:03          6       0          0   \n",
       "\n",
       "   active  incident_rate  case_fatality_ratio  \n",
       "0     NaN            NaN                  NaN  \n",
       "1     NaN            NaN                  NaN  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT *\n",
    "FROM datamart.new_cases\n",
    "WHERE location_id=-278257842025680577 AND last_update='2020-03-08 17:33:03'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "worthy-greenhouse",
   "metadata": {},
   "source": [
    "##### remove duplicate by SELECT DISTINCT: the single duplicate record is an exact copy that cannot be ranked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "comprehensive-migration",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.new_cases AS\n",
    "SELECT DISTINCT *\n",
    "FROM datamart.new_cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "connected-sodium",
   "metadata": {},
   "source": [
    "#### PK re-re-check: no duplicates if same number"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "favorite-bloom",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>distinct_PK</th>\n",
       "      <th>cases_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1475736</td>\n",
       "      <td>1475736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   distinct_PK  cases_count\n",
       "0      1475736      1475736"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS distinct_PK, (SELECT count(*) FROM datamart.new_cases) AS cases_count\n",
    "FROM (\n",
    "    SELECT DISTINCT location_id, last_update\n",
    "    FROM datamart.new_cases\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "damaged-aviation",
   "metadata": {},
   "source": [
    "#### FK check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "noble-poultry",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>foreign_key_violations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   foreign_key_violations\n",
       "0                       0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT count(*) AS foreign_key_violations\n",
    "FROM datamart.new_cases AS c\n",
    "LEFT JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id\n",
    "WHERE l.id IS null"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "removed-spray",
   "metadata": {},
   "source": [
    "#### Replace cases with new_cases' data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "loaded-browse",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE TABLE datamart.cases AS\n",
    "SELECT *\n",
    "FROM datamart.new_cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "genetic-uganda",
   "metadata": {},
   "source": [
    "### 3 SQL Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "massive-species",
   "metadata": {},
   "source": [
    "#### Display the population of Texans affected by COVID-19 based on the average incident rate of the most recent updated Texan cases, and distribute the population by the gross rent they pay. (Assuming that all Texans occupy housing units that require rent, that the percentages in the housing table is applicable to Texans as is, and that the total Texas population is 8.74% of the US's total population)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "center-coupon",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Gross_Rent</th>\n",
       "      <th>Estimated_COVID_Affected_Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>$1,000 to $1,499</td>\n",
       "      <td>715547.538817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>$1,500 to $1,999</td>\n",
       "      <td>333922.184781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>$2,000 to $2,499</td>\n",
       "      <td>133568.873912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>$2,500 to $2,999</td>\n",
       "      <td>57243.803105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>$3,000 or more</td>\n",
       "      <td>57243.803105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>$500 to $999</td>\n",
       "      <td>863427.363506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Less than $500</td>\n",
       "      <td>224204.895496</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Gross_Rent  Estimated_COVID_Affected_Population\n",
       "0  $1,000 to $1,499                        715547.538817\n",
       "1  $1,500 to $1,999                        333922.184781\n",
       "2  $2,000 to $2,499                        133568.873912\n",
       "3  $2,500 to $2,999                         57243.803105\n",
       "4    $3,000 or more                         57243.803105\n",
       "5      $500 to $999                        863427.363506\n",
       "6    Less than $500                        224204.895496"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT label AS Gross_Rent, (h.percent/100)*\n",
    "                                (((SELECT AVG(incident_rate)\n",
    "                                    FROM datamart.cases \n",
    "                                    WHERE last_update=(SELECT MAX(last_update) \n",
    "                                                        FROM datamart.cases AS c \n",
    "                                                        JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id \n",
    "                                                        WHERE l.state='Texas' AND l.country='US'))/100000)\n",
    "                                *\n",
    "                                ((SELECT estimate FROM datamart.demographics WHERE label='Total population')*8.74/100)) AS Estimated_COVID_Affected_Population \n",
    "FROM datamart.housing AS h\n",
    "WHERE subcategory='gross rent' AND percent IS NOT null\n",
    "ORDER BY Gross_Rent"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ideal-combining",
   "metadata": {},
   "source": [
    "#### Display the estimated number of confirmed US COVID-19 cases during January of 2021 per current educational status (those in school) as well as highest educational attainment (those out of school), ordered by ascending number of confimed cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "boxed-orlando",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Enrollment</th>\n",
       "      <th>Estimated_COVID_Confirmed_Cases</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kindergarten</td>\n",
       "      <td>36234778.600000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Less than 9th grade</td>\n",
       "      <td>36959474.172000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9th to 12th grade, no diploma</td>\n",
       "      <td>50003994.468000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Associate's degree</td>\n",
       "      <td>61599123.620000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Graduate or professional degree</td>\n",
       "      <td>89862250.928000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Bachelor's degree</td>\n",
       "      <td>143489723.256000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Some college, no degree</td>\n",
       "      <td>147837896.688000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>High school (grades 9-12)</td>\n",
       "      <td>152186070.120000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>High school graduate (includes equivalency)</td>\n",
       "      <td>195667804.440000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>College or graduate school</td>\n",
       "      <td>199291282.300000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Bachelor's degree or higher</td>\n",
       "      <td>232627278.612000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Elementary school (grades 1-8)</td>\n",
       "      <td>292777011.088000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>High school graduate or higher</td>\n",
       "      <td>637732103.360000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     Enrollment  \\\n",
       "0                                  Kindergarten   \n",
       "1                           Less than 9th grade   \n",
       "2                 9th to 12th grade, no diploma   \n",
       "3                            Associate's degree   \n",
       "4               Graduate or professional degree   \n",
       "5                             Bachelor's degree   \n",
       "6                       Some college, no degree   \n",
       "7                     High school (grades 9-12)   \n",
       "8   High school graduate (includes equivalency)   \n",
       "9                    College or graduate school   \n",
       "10                  Bachelor's degree or higher   \n",
       "11               Elementary school (grades 1-8)   \n",
       "12               High school graduate or higher   \n",
       "\n",
       "   Estimated_COVID_Confirmed_Cases  \n",
       "0               36234778.600000000  \n",
       "1               36959474.172000000  \n",
       "2               50003994.468000000  \n",
       "3               61599123.620000000  \n",
       "4               89862250.928000000  \n",
       "5              143489723.256000000  \n",
       "6              147837896.688000000  \n",
       "7              152186070.120000000  \n",
       "8              195667804.440000000  \n",
       "9              199291282.300000000  \n",
       "10             232627278.612000000  \n",
       "11             292777011.088000000  \n",
       "12             637732103.360000000  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT s.label AS Enrollment, (s.percent/100)*\n",
    "                                (SELECT SUM(c.Confirmed)\n",
    "                                    FROM datamart.cases AS c \n",
    "                                    JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id \n",
    "                                    WHERE l.country='US' AND c.last_update between '2021-01-01' AND '2021-01-31 23:59:59') AS Estimated_COVID_Confirmed_Cases\n",
    "FROM datamart.social AS s\n",
    "WHERE (s.subcategory='School Enrollment' OR s.subcategory = 'Educational Attainment') AND s.percent IS NOT null\n",
    "ORDER BY Estimated_COVID_Confirmed_Cases"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "printable-painting",
   "metadata": {},
   "source": [
    "#### Display the estimated number of deaths per age group in the US during 2021 (up until April 14, 2021), ordered by estimated deaths in descending order"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fancy-representation",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Age_Group</th>\n",
       "      <th>Estimated_Deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16 years and over</td>\n",
       "      <td>40238436.800000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21 years and over</td>\n",
       "      <td>36868467.718000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Under 18 years</td>\n",
       "      <td>11367358.396000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>62 years and over</td>\n",
       "      <td>9657224.832000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>25 to 34 years</td>\n",
       "      <td>6991428.394000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>45 to 54 years</td>\n",
       "      <td>6538745.980000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>35 to 44 years</td>\n",
       "      <td>6337553.796000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>65 to 74 years</td>\n",
       "      <td>4577122.186000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>20 to 24 years</td>\n",
       "      <td>3420267.128000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>55 to 59 years</td>\n",
       "      <td>3369969.082000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>15 to 19 years</td>\n",
       "      <td>3269372.990000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10 to 14 years</td>\n",
       "      <td>3219074.944000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5 to 9 years</td>\n",
       "      <td>3118478.852000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>60 to 64 years</td>\n",
       "      <td>3118478.852000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Under 5 years</td>\n",
       "      <td>3068180.806000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>75 to 84 years</td>\n",
       "      <td>2313710.116000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>85 years and over</td>\n",
       "      <td>955662.874000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Age_Group    Estimated_Deaths\n",
       "0   16 years and over  40238436.800000000\n",
       "1   21 years and over  36868467.718000000\n",
       "2      Under 18 years  11367358.396000000\n",
       "3   62 years and over   9657224.832000000\n",
       "4      25 to 34 years   6991428.394000000\n",
       "5      45 to 54 years   6538745.980000000\n",
       "6      35 to 44 years   6337553.796000000\n",
       "7      65 to 74 years   4577122.186000000\n",
       "8      20 to 24 years   3420267.128000000\n",
       "9      55 to 59 years   3369969.082000000\n",
       "10     15 to 19 years   3269372.990000000\n",
       "11     10 to 14 years   3219074.944000000\n",
       "12       5 to 9 years   3118478.852000000\n",
       "13     60 to 64 years   3118478.852000000\n",
       "14      Under 5 years   3068180.806000000\n",
       "15     75 to 84 years   2313710.116000000\n",
       "16  85 years and over    955662.874000000"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT d.label AS Age_Group, (d.percent/100)*\n",
    "                                        (SELECT SUM(deaths)\n",
    "                                        FROM datamart.cases AS c\n",
    "                                        JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id\n",
    "                                        WHERE l.country='US' AND last_update between '2021-01-01' and '2021-04-14 23:59:59') AS Estimated_Deaths\n",
    "                                    \n",
    "FROM datamart.demographics as d\n",
    "WHERE (subcategory1 = 'age' AND subcategory2 is null AND d.percent is not null)\n",
    "ORDER BY Estimated_Deaths DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "metropolitan-dependence",
   "metadata": {},
   "source": [
    "### Create Views from Above SQL Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "convinced-migration",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE VIEW reports.estimated_texan_covid_population_by_gross_rent AS\n",
    "SELECT label AS Gross_Rent, (h.percent/100)*\n",
    "                                (((SELECT AVG(incident_rate)\n",
    "                                    FROM datamart.cases \n",
    "                                    WHERE last_update=(SELECT MAX(last_update) \n",
    "                                                        FROM datamart.cases AS c \n",
    "                                                        JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id \n",
    "                                                        WHERE l.state='Texas' AND l.country='US'))/100000)\n",
    "                                *\n",
    "                                ((SELECT estimate FROM datamart.demographics WHERE label='Total population')*8.74/100)) AS Estimated_COVID_Affected_Population \n",
    "FROM datamart.housing AS h\n",
    "WHERE subcategory='gross rent' AND percent IS NOT null\n",
    "ORDER BY Gross_Rent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "indirect-brook",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE VIEW reports.estimated_confirmed_cases_by_education AS\n",
    "SELECT s.label AS Enrollment, (s.percent/100)*\n",
    "                                (SELECT SUM(c.Confirmed)\n",
    "                                    FROM datamart.cases AS c \n",
    "                                    JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id \n",
    "                                    WHERE l.country='US' AND c.last_update between '2021-01-01' AND '2021-01-31 23:59:59') AS Estimated_COVID_Confirmed_Cases\n",
    "FROM datamart.social AS s\n",
    "WHERE (s.subcategory='School Enrollment' OR s.subcategory = 'Educational Attainment') AND s.percent IS NOT null\n",
    "ORDER BY Estimated_COVID_Confirmed_Cases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "pursuant-snowboard",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE VIEW reports.estimated_deaths_by_age_2021 AS\n",
    "SELECT d.label AS Age_Group, (d.percent/100)*\n",
    "                                        (SELECT SUM(deaths)\n",
    "                                        FROM datamart.cases AS c\n",
    "                                        JOIN datamart.locations_Dataflow AS l ON c.location_id=l.id\n",
    "                                        WHERE l.country='US' AND last_update between '2021-01-01' and '2021-04-14 23:59:59') AS Estimated_Deaths\n",
    "                                    \n",
    "FROM datamart.demographics as d\n",
    "WHERE (subcategory1 = 'age' AND subcategory2 is null AND d.percent is not null)\n",
    "ORDER BY Estimated_Deaths DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "treated-treasury",
   "metadata": {},
   "source": [
    "### Temp tables were removed via BigQuery's UI"
   ]
  }
 ],
 "metadata": {
  "environment": {
   "name": "common-cpu.mnightly-2021-01-20-debian-10-test",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/base-cpu:mnightly-2021-01-20-debian-10-test"
  },
  "kernelspec": {
   "display_name": "Python [conda env:root] *",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}