kaggle_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Aggregate Queries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Below there are 6 examples of aggregate queries for our dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This updated query joins on all dates within the entity tables Date and State to compare the State temperature with the Global Average Temperature on the same date. This join is then grouped by Date and State, and then Ordered by Date, ultimately showing the max recorded state temperature and global temperate (if various records were taken on the same date) sorted by ascending order of date and alphabetical order of state name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Date</th>\n",
       "      <th>State</th>\n",
       "      <th>StateTemp</th>\n",
       "      <th>GlobalTemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>7.444</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Arkhangel'Sk</td>\n",
       "      <td>-13.213</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Belgorod</td>\n",
       "      <td>-6.624</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Bryansk</td>\n",
       "      <td>-5.881</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Chuvash</td>\n",
       "      <td>-11.199</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>City Of St. Petersburg</td>\n",
       "      <td>-4.101</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>-4.043</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>-0.466</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>District Of Columbia</td>\n",
       "      <td>-1.080</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Florida</td>\n",
       "      <td>15.298</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Georgia (State)</td>\n",
       "      <td>8.271</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1750-01-01</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>-3.426</td>\n",
       "      <td>3.034</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date                   State  StateTemp  GlobalTemp\n",
       "0   1750-01-01                 Alabama      7.444       3.034\n",
       "1   1750-01-01            Arkhangel'Sk    -13.213       3.034\n",
       "2   1750-01-01                Belgorod     -6.624       3.034\n",
       "3   1750-01-01                 Bryansk     -5.881       3.034\n",
       "4   1750-01-01                 Chuvash    -11.199       3.034\n",
       "5   1750-01-01  City Of St. Petersburg     -4.101       3.034\n",
       "6   1750-01-01             Connecticut     -4.043       3.034\n",
       "7   1750-01-01                Delaware     -0.466       3.034\n",
       "8   1750-01-01    District Of Columbia     -1.080       3.034\n",
       "9   1750-01-01                 Florida     15.298       3.034\n",
       "10  1750-01-01         Georgia (State)      8.271       3.034\n",
       "11  1750-01-01                Illinois     -3.426       3.034"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT State.dt as Date, \n",
    "State.State as State,\n",
    "MAX(State.AverageTemperature) as StateTemp,\n",
    "MAX(Date.LandAverageTemperature) as GlobalTemp\n",
    "FROM kaggle_modeled.State\n",
    "JOIN kaggle_modeled.Date\n",
    "ON State.dt = Date.dt\n",
    "WHERE State.AverageTemperature IS NOT NULL\n",
    "GROUP BY Date, State\n",
    "ORDER BY Date, State\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query joins on Country for our State and Country table to return average aggregated state and country temperatures ordered by date recorded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Date</th>\n",
       "      <th>State</th>\n",
       "      <th>AvgStateTemp</th>\n",
       "      <th>AvgCountryTemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Newfoundland And Labrador</td>\n",
       "      <td>6.999</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Saskatchewan</td>\n",
       "      <td>9.808</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Manitoba</td>\n",
       "      <td>8.937</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Alberta</td>\n",
       "      <td>8.772</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>16.899</td>\n",
       "      <td>15.420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Prince Edward Island</td>\n",
       "      <td>12.302</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>New Brunswick</td>\n",
       "      <td>11.316</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Nova Scotia</td>\n",
       "      <td>12.612</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>16.926</td>\n",
       "      <td>15.420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Ontario</td>\n",
       "      <td>10.016</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Nunavut</td>\n",
       "      <td>-1.377</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1768-09-01</td>\n",
       "      <td>Northwest Territories</td>\n",
       "      <td>2.866</td>\n",
       "      <td>5.257</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date                      State  AvgStateTemp  AvgCountryTemp\n",
       "0   1768-09-01  Newfoundland And Labrador         6.999           5.257\n",
       "1   1768-09-01               Saskatchewan         9.808           5.257\n",
       "2   1768-09-01                   Manitoba         8.937           5.257\n",
       "3   1768-09-01                    Alberta         8.772           5.257\n",
       "4   1768-09-01                       Iowa        16.899          15.420\n",
       "5   1768-09-01       Prince Edward Island        12.302           5.257\n",
       "6   1768-09-01              New Brunswick        11.316           5.257\n",
       "7   1768-09-01                Nova Scotia        12.612           5.257\n",
       "8   1768-09-01                       Ohio        16.926          15.420\n",
       "9   1768-09-01                    Ontario        10.016           5.257\n",
       "10  1768-09-01                    Nunavut        -1.377           5.257\n",
       "11  1768-09-01      Northwest Territories         2.866           5.257"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT State.dt as Date, \n",
    "State.State as State,\n",
    "AVG(State.AverageTemperature) as AvgStateTemp,\n",
    "AVG(Country.AverageTemperature) as AvgCountryTemp\n",
    "FROM kaggle_modeled.State\n",
    "JOIN kaggle_modeled.Country\n",
    "ON State.dt = Country.dt and State.Country = Country.Country\n",
    "WHERE Country.AverageTemperature IS NOT NULL\n",
    "GROUP BY Date, State\n",
    "ORDER BY Date\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The below query joins the Country beam and City beam tables to display aggregated averages for city and country temperature. These results are then grouped and ordered by ascending city temperature and date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>City</th>\n",
       "      <th>CityTemp</th>\n",
       "      <th>CountryTemp</th>\n",
       "      <th>Country</th>\n",
       "      <th>RecordingDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-42.704</td>\n",
       "      <td>-24.371</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1979-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kyzyl</td>\n",
       "      <td>-41.101</td>\n",
       "      <td>-30.497</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1893-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-39.919</td>\n",
       "      <td>-27.705</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1966-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-39.683</td>\n",
       "      <td>-27.136</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1974-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-39.403</td>\n",
       "      <td>-28.584</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1979-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kyzyl</td>\n",
       "      <td>-39.038</td>\n",
       "      <td>-29.443</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1919-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Kyzyl</td>\n",
       "      <td>-38.951</td>\n",
       "      <td>-28.760</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1872-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Kyzyl</td>\n",
       "      <td>-38.906</td>\n",
       "      <td>-29.789</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1969-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-38.784</td>\n",
       "      <td>-29.561</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1969-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-38.715</td>\n",
       "      <td>-26.354</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1922-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Kyzyl</td>\n",
       "      <td>-38.693</td>\n",
       "      <td>-29.659</td>\n",
       "      <td>Russia</td>\n",
       "      <td>1900-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Norilsk</td>\n",
       "      <td>-38.447</td>\n",
       "      <td>-24.882</td>\n",
       "      <td>Russia</td>\n",
       "      <td>2000-12-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       City  CityTemp  CountryTemp Country RecordingDate\n",
       "0   Norilsk   -42.704      -24.371  Russia    1979-02-01\n",
       "1     Kyzyl   -41.101      -30.497  Russia    1893-01-01\n",
       "2   Norilsk   -39.919      -27.705  Russia    1966-02-01\n",
       "3   Norilsk   -39.683      -27.136  Russia    1974-01-01\n",
       "4   Norilsk   -39.403      -28.584  Russia    1979-01-01\n",
       "5     Kyzyl   -39.038      -29.443  Russia    1919-01-01\n",
       "6     Kyzyl   -38.951      -28.760  Russia    1872-01-01\n",
       "7     Kyzyl   -38.906      -29.789  Russia    1969-01-01\n",
       "8   Norilsk   -38.784      -29.561  Russia    1969-02-01\n",
       "9   Norilsk   -38.715      -26.354  Russia    1922-02-01\n",
       "10    Kyzyl   -38.693      -29.659  Russia    1900-01-01\n",
       "11  Norilsk   -38.447      -24.882  Russia    2000-12-01"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT City_Beam_DF.City, \n",
    "AVG(City_Beam_DF.AverageTemperature) as CityTemp, \n",
    "AVG(Country_Beam_DF.AverageTemperature) as CountryTemp,\n",
    "Country_Beam_DF.Country,\n",
    "City_Beam_DF.dt as RecordingDate\n",
    "FROM kaggle_modeled.City_Beam_DF\n",
    "LEFT JOIN kaggle_modeled.Country_Beam_DF\n",
    "ON Country_Beam_DF.dt = City_Beam_DF.dt and Country_Beam_DF.Country = City_Beam_DF.Country\n",
    "WHERE Country_Beam_DF.AverageTemperature IS NOT NULL\n",
    "GROUP BY City, Country, RecordingDate\n",
    "ORDER BY CityTemp, RecordingDate\n",
    "Limit 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The below query joins the Country beam and City beam tables to display aggregated averages for city and country temperature. These results are then grouped and ordered by ascending city temperature and date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>City</th>\n",
       "      <th>Date</th>\n",
       "      <th>AvgTemp</th>\n",
       "      <th>AvgTempUncertainty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Ankara</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>11.088</td>\n",
       "      <td>4.733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Montreal</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>4.743</td>\n",
       "      <td>3.346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Izmir</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>17.982</td>\n",
       "      <td>4.911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New York</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>8.669</td>\n",
       "      <td>3.393</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Moscow</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>4.626</td>\n",
       "      <td>5.901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kiev</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>7.147</td>\n",
       "      <td>6.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>11.196</td>\n",
       "      <td>3.692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Rome</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>11.814</td>\n",
       "      <td>6.516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Paris</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>9.972</td>\n",
       "      <td>7.810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Istanbul</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>14.596</td>\n",
       "      <td>5.076</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Madrid</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>10.916</td>\n",
       "      <td>5.871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Saint Petersburg</td>\n",
       "      <td>1755-10-01</td>\n",
       "      <td>22.492</td>\n",
       "      <td>3.795</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                City        Date  AvgTemp  AvgTempUncertainty\n",
       "0             Ankara  1755-10-01   11.088               4.733\n",
       "1           Montreal  1755-10-01    4.743               3.346\n",
       "2              Izmir  1755-10-01   17.982               4.911\n",
       "3           New York  1755-10-01    8.669               3.393\n",
       "4             Moscow  1755-10-01    4.626               5.901\n",
       "5               Kiev  1755-10-01    7.147               6.002\n",
       "6            Chicago  1755-10-01   11.196               3.692\n",
       "7               Rome  1755-10-01   11.814               6.516\n",
       "8              Paris  1755-10-01    9.972               7.810\n",
       "9           Istanbul  1755-10-01   14.596               5.076\n",
       "10            Madrid  1755-10-01   10.916               5.871\n",
       "11  Saint Petersburg  1755-10-01   22.492               3.795"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery \n",
    "SELECT City_Beam_DF.City,\n",
    "City_Beam_DF.dt as Date,\n",
    "AVG(City_Beam_DF.AverageTemperature) as AvgTemp,\n",
    "AVG(City_Beam_DF.AverageTemperatureUncertainty) as AvgTempUncertainty\n",
    "FROM kaggle_modeled.City_Beam_DF\n",
    "WHERE City_Beam_DF.AverageTemperature IS NOT NULL\n",
    "GROUP BY City,Date\n",
    "HAVING MIN(City_Beam_DF.major_city) = 1\n",
    "ORDER BY Date\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The below query returns the global average land temperature, along with the aggregated max land max temperature and min land min temperature in ascending order by average land temperature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>Date</th>\n",
       "      <th>AvgLandTemp</th>\n",
       "      <th>AvgLandMaxTemp</th>\n",
       "      <th>AvgLandMinTemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1861-01-01</td>\n",
       "      <td>0.404</td>\n",
       "      <td>7.743</td>\n",
       "      <td>-3.256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1893-01-01</td>\n",
       "      <td>0.500</td>\n",
       "      <td>5.9</td>\n",
       "      <td>-5.345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1862-01-01</td>\n",
       "      <td>0.685</td>\n",
       "      <td>7.361</td>\n",
       "      <td>-4.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1850-01-01</td>\n",
       "      <td>0.749</td>\n",
       "      <td>8.242</td>\n",
       "      <td>-3.206</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1887-01-01</td>\n",
       "      <td>0.824</td>\n",
       "      <td>6.864</td>\n",
       "      <td>-4.678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1885-01-01</td>\n",
       "      <td>1.003</td>\n",
       "      <td>6.421</td>\n",
       "      <td>-4.621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1854-01-01</td>\n",
       "      <td>1.281</td>\n",
       "      <td>8.786</td>\n",
       "      <td>-3.552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1895-01-01</td>\n",
       "      <td>1.295</td>\n",
       "      <td>6.961</td>\n",
       "      <td>-4.319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1909-01-01</td>\n",
       "      <td>1.395</td>\n",
       "      <td>7.121</td>\n",
       "      <td>-4.298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1886-01-01</td>\n",
       "      <td>1.436</td>\n",
       "      <td>7.023</td>\n",
       "      <td>-3.755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1871-01-01</td>\n",
       "      <td>1.447</td>\n",
       "      <td>7.962</td>\n",
       "      <td>-2.937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1883-01-01</td>\n",
       "      <td>1.460</td>\n",
       "      <td>7.064</td>\n",
       "      <td>-4.519</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date  AvgLandTemp AvgLandMaxTemp AvgLandMinTemp\n",
       "0   1861-01-01        0.404          7.743         -3.256\n",
       "1   1893-01-01        0.500            5.9         -5.345\n",
       "2   1862-01-01        0.685          7.361          -4.11\n",
       "3   1850-01-01        0.749          8.242         -3.206\n",
       "4   1887-01-01        0.824          6.864         -4.678\n",
       "5   1885-01-01        1.003          6.421         -4.621\n",
       "6   1854-01-01        1.281          8.786         -3.552\n",
       "7   1895-01-01        1.295          6.961         -4.319\n",
       "8   1909-01-01        1.395          7.121         -4.298\n",
       "9   1886-01-01        1.436          7.023         -3.755\n",
       "10  1871-01-01        1.447          7.962         -2.937\n",
       "11  1883-01-01        1.460          7.064         -4.519"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Date.dt as Date,\n",
    "Date.LandAverageTemperature as AvgLandTemp,\n",
    "MAX(Date.LandMaxTemperature) as AvgLandMaxTemp,\n",
    "MIN(Date.LandMinTemperature) as AvgLandMinTemp,\n",
    "FROM kaggle_modeled.Date\n",
    "WHERE Date.LandAverageTemperature IS NOT NULL AND\n",
    "Date.LandMaxTemperature IS NOT NULL AND\n",
    "Date.LandMinTemperature IS NOT NULL\n",
    "GROUP BY Date,AvgLandTemp\n",
    "ORDER BY AvgLandTemp\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The below query return the aggregated max average temperature and average temperature uncertainty from the date table for results in the current century (2000 and onward)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Date</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>AvgTemp</th>\n",
       "      <th>AvgTempUncertainty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Goa</td>\n",
       "      <td>India</td>\n",
       "      <td>25.379</td>\n",
       "      <td>0.266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Acre</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>25.934</td>\n",
       "      <td>0.379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Amur</td>\n",
       "      <td>Russia</td>\n",
       "      <td>-28.286</td>\n",
       "      <td>0.572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>United States</td>\n",
       "      <td>-5.230</td>\n",
       "      <td>0.184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000-01-01</td>\n",
       "      <td>Komi</td>\n",
       "      <td>Russia</td>\n",
       "      <td>-18.236</td>\n",
       "      <td>0.529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39579</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>British Columbia</td>\n",
       "      <td>Canada</td>\n",
       "      <td>10.332</td>\n",
       "      <td>2.327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39580</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>District Of Columbia</td>\n",
       "      <td>United States</td>\n",
       "      <td>19.643</td>\n",
       "      <td>1.050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39581</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>Prince Edward Island</td>\n",
       "      <td>Canada</td>\n",
       "      <td>15.021</td>\n",
       "      <td>1.778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39582</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>Northwest Territories</td>\n",
       "      <td>Canada</td>\n",
       "      <td>5.831</td>\n",
       "      <td>4.031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39583</th>\n",
       "      <td>2013-09-01</td>\n",
       "      <td>Newfoundland And Labrador</td>\n",
       "      <td>Canada</td>\n",
       "      <td>9.340</td>\n",
       "      <td>2.760</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>39584 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Date                      State        Country  AvgTemp  \\\n",
       "0      2000-01-01                        Goa          India   25.379   \n",
       "1      2000-01-01                       Acre         Brazil   25.934   \n",
       "2      2000-01-01                       Amur         Russia  -28.286   \n",
       "3      2000-01-01                       Iowa  United States   -5.230   \n",
       "4      2000-01-01                       Komi         Russia  -18.236   \n",
       "...           ...                        ...            ...      ...   \n",
       "39579  2013-09-01           British Columbia         Canada   10.332   \n",
       "39580  2013-09-01       District Of Columbia  United States   19.643   \n",
       "39581  2013-09-01       Prince Edward Island         Canada   15.021   \n",
       "39582  2013-09-01      Northwest Territories         Canada    5.831   \n",
       "39583  2013-09-01  Newfoundland And Labrador         Canada    9.340   \n",
       "\n",
       "       AvgTempUncertainty  \n",
       "0                   0.266  \n",
       "1                   0.379  \n",
       "2                   0.572  \n",
       "3                   0.184  \n",
       "4                   0.529  \n",
       "...                   ...  \n",
       "39579               2.327  \n",
       "39580               1.050  \n",
       "39581               1.778  \n",
       "39582               4.031  \n",
       "39583               2.760  \n",
       "\n",
       "[39584 rows x 5 columns]"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT State_Beam_DF.dt as Date,\n",
    "State_Beam_DF.State as State,\n",
    "State_Beam_DF.Country as Country,\n",
    "MAX(State_Beam_DF.AverageTemperature) as AvgTemp,\n",
    "MAX(State_Beam_DF.AverageTemperatureUncertainty) as AvgTempUncertainty\n",
    "FROM kaggle_modeled.State_Beam_DF\n",
    "GROUP BY Date, State, Country\n",
    "HAVING MAX(State_Beam_DF.dt) > '1999-12-31'\n",
    "ORDER BY Date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating the views for Data Studio based on 2 of our queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "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": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE VIEW kaggle_modeled.v_Global_Land_Temperature_Variations AS\n",
    "  SELECT dt as Date,\n",
    "  LandAverageTemperature as AvgLandTemp,\n",
    "  MAX(LandMaxTemperature) as AvgLandMaxTemp,\n",
    "  MIN(LandMinTemperature) as AvgLandMinTemp,\n",
    "  FROM `electric-spark-266716.kaggle_modeled.Date`\n",
    "  WHERE LandAverageTemperature IS NOT NULL AND\n",
    "  LandMaxTemperature IS NOT NULL AND\n",
    "  LandMinTemperature IS NOT NULL\n",
    "  GROUP BY Date,AvgLandTemp\n",
    "  ORDER BY AvgLandTemp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "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": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE VIEW kaggle_modeled.v_Major_City_Temperature AS\n",
    "    SELECT City,\n",
    "    dt as Date,\n",
    "    AVG(AverageTemperature) as AvgTemp,\n",
    "    AVG(AverageTemperatureUncertainty) as AvgTempUncertainty\n",
    "    FROM `electric-spark-266716.kaggle_modeled.City_Beam_DF`\n",
    "    WHERE AverageTemperature IS NOT NULL\n",
    "    GROUP BY City,Date\n",
    "    HAVING MIN(major_city) = 1\n",
    "    ORDER BY Date\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## **Queries and Subqueries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following query finds all dates and the associated temperatures that are higher than the average temperatue of the United States."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1822-03-01</td>\n",
       "      <td>28.749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1815-03-01</td>\n",
       "      <td>25.881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1814-07-01</td>\n",
       "      <td>23.781</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1939-09-01</td>\n",
       "      <td>25.296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1936-03-01</td>\n",
       "      <td>27.030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315686</th>\n",
       "      <td>2009-04-01</td>\n",
       "      <td>12.296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315687</th>\n",
       "      <td>1930-10-01</td>\n",
       "      <td>12.544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315688</th>\n",
       "      <td>1971-11-01</td>\n",
       "      <td>12.646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315689</th>\n",
       "      <td>1935-11-01</td>\n",
       "      <td>14.277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>315690</th>\n",
       "      <td>1986-12-01</td>\n",
       "      <td>15.334</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>315691 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                dt  AverageTemperature\n",
       "0       1822-03-01              28.749\n",
       "1       1815-03-01              25.881\n",
       "2       1814-07-01              23.781\n",
       "3       1939-09-01              25.296\n",
       "4       1936-03-01              27.030\n",
       "...            ...                 ...\n",
       "315686  2009-04-01              12.296\n",
       "315687  1930-10-01              12.544\n",
       "315688  1971-11-01              12.646\n",
       "315689  1935-11-01              14.277\n",
       "315690  1986-12-01              15.334\n",
       "\n",
       "[315691 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT s.dt, s.AverageTemperature\n",
    "FROM kaggle_modeled.State_Beam_DF as s\n",
    "WHERE s.AverageTemperature > \n",
    "    (SELECT AVG(AverageTemperature)\n",
    "     FROM kaggle_modeled.State_Beam_DF\n",
    "     WHERE Country = \"United States\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query finds the cities/countries and their associated uncertainties for dates with valid AverageTemperatures and higher uncertainties than the minimum."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>CityTempUncertainty</th>\n",
       "      <th>CountryTempUncertainty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kroonstad</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kroonstad</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Kroonstad</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Kroonstad</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Kroonstad</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5614</th>\n",
       "      <td>Welkom</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5615</th>\n",
       "      <td>Welkom</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5616</th>\n",
       "      <td>Welkom</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5617</th>\n",
       "      <td>Welkom</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5618</th>\n",
       "      <td>Welkom</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>0.034</td>\n",
       "      <td>0.390</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5619 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           City       Country  CityTempUncertainty  CountryTempUncertainty\n",
       "0     Kroonstad  South Africa                0.034                   0.250\n",
       "1     Kroonstad  South Africa                0.034                   0.250\n",
       "2     Kroonstad  South Africa                0.034                   0.250\n",
       "3     Kroonstad  South Africa                0.034                   0.250\n",
       "4     Kroonstad  South Africa                0.034                   0.250\n",
       "...         ...           ...                  ...                     ...\n",
       "5614     Welkom  South Africa                0.034                   0.265\n",
       "5615     Welkom  South Africa                0.034                   0.265\n",
       "5616     Welkom  South Africa                0.034                   0.265\n",
       "5617     Welkom  South Africa                0.034                   0.390\n",
       "5618     Welkom  South Africa                0.034                   0.390\n",
       "\n",
       "[5619 rows x 4 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT c.City as City,\n",
    "t.Country as Country,\n",
    "c.AverageTemperatureUncertainty as CityTempUncertainty,\n",
    "t.AverageTemperatureUncertainty as CountryTempUncertainty,\n",
    "FROM kaggle_modeled.City_Beam_DF as c\n",
    "LEFT JOIN kaggle_modeled.Country_Beam_DF as t\n",
    "ON c.Country = t.Country\n",
    "WHERE c.AverageTemperatureUncertainty =\n",
    "    (SELECT MIN(AverageTemperatureUncertainty)\n",
    "     FROM kaggle_modeled.City_Beam_DF\n",
    "     WHERE AverageTemperature is not NULL)\n",
    "ORDER BY c.City"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following query selects the dates and states and the corresponding max state temperatures and global temperatures, with an AverageTemperatureUncertainty greater than the maximum in Texas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Date</th>\n",
       "      <th>State</th>\n",
       "      <th>StateTemp</th>\n",
       "      <th>GlobalTemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1752-01-01</td>\n",
       "      <td>Komi</td>\n",
       "      <td>-21.183</td>\n",
       "      <td>0.348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1752-01-01</td>\n",
       "      <td>Nenets</td>\n",
       "      <td>-20.383</td>\n",
       "      <td>0.348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1752-03-01</td>\n",
       "      <td>Komi</td>\n",
       "      <td>-8.854</td>\n",
       "      <td>5.806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1752-03-01</td>\n",
       "      <td>Nenets</td>\n",
       "      <td>-12.504</td>\n",
       "      <td>5.806</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1752-04-01</td>\n",
       "      <td>Komi</td>\n",
       "      <td>-3.300</td>\n",
       "      <td>8.265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12728</th>\n",
       "      <td>1933-01-01</td>\n",
       "      <td>Koryak</td>\n",
       "      <td>-24.759</td>\n",
       "      <td>2.169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12729</th>\n",
       "      <td>1933-12-01</td>\n",
       "      <td>Chukot</td>\n",
       "      <td>-23.719</td>\n",
       "      <td>3.104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12730</th>\n",
       "      <td>1933-12-01</td>\n",
       "      <td>Koryak</td>\n",
       "      <td>-13.814</td>\n",
       "      <td>3.104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12731</th>\n",
       "      <td>1935-12-01</td>\n",
       "      <td>Chukot</td>\n",
       "      <td>-22.914</td>\n",
       "      <td>3.423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12732</th>\n",
       "      <td>2013-02-01</td>\n",
       "      <td>Maga Buryatdan</td>\n",
       "      <td>-32.288</td>\n",
       "      <td>4.222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12733 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Date           State  StateTemp  GlobalTemp\n",
       "0      1752-01-01            Komi    -21.183       0.348\n",
       "1      1752-01-01          Nenets    -20.383       0.348\n",
       "2      1752-03-01            Komi     -8.854       5.806\n",
       "3      1752-03-01          Nenets    -12.504       5.806\n",
       "4      1752-04-01            Komi     -3.300       8.265\n",
       "...           ...             ...        ...         ...\n",
       "12728  1933-01-01          Koryak    -24.759       2.169\n",
       "12729  1933-12-01          Chukot    -23.719       3.104\n",
       "12730  1933-12-01          Koryak    -13.814       3.104\n",
       "12731  1935-12-01          Chukot    -22.914       3.423\n",
       "12732  2013-02-01  Maga Buryatdan    -32.288       4.222\n",
       "\n",
       "[12733 rows x 4 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT State.dt as Date,  \n",
    "State.State as State, \n",
    "MAX(State.AverageTemperature) as StateTemp, \n",
    "MAX(Date.LandAverageTemperature) as GlobalTemp \n",
    "FROM kaggle_modeled.State \n",
    "JOIN kaggle_modeled.Date \n",
    "ON State.dt = Date.dt \n",
    "WHERE AverageTemperatureUncertainty >       \n",
    "    (SELECT MAX(s.AverageTemperatureUncertainty)\n",
    "     FROM kaggle_modeled.State as s\n",
    "     where s.State = \"Texas\")\n",
    "GROUP BY Date, State\n",
    "ORDER BY Date, State\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query finds the City, AverageTemperature, Longitude, Latitude of all cities in India on dates that have an average temperature greater than the country's maximum. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>City</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jiroft</td>\n",
       "      <td>32.044</td>\n",
       "      <td>57.27E</td>\n",
       "      <td>28.13N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Mecca</td>\n",
       "      <td>31.843</td>\n",
       "      <td>40.67E</td>\n",
       "      <td>21.70N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mecca</td>\n",
       "      <td>32.065</td>\n",
       "      <td>40.67E</td>\n",
       "      <td>21.70N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rabak</td>\n",
       "      <td>33.028</td>\n",
       "      <td>32.20E</td>\n",
       "      <td>13.66N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Damaturu</td>\n",
       "      <td>31.955</td>\n",
       "      <td>11.51E</td>\n",
       "      <td>12.05N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146169</th>\n",
       "      <td>Tempe</td>\n",
       "      <td>32.086</td>\n",
       "      <td>112.02W</td>\n",
       "      <td>32.95N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146170</th>\n",
       "      <td>Peoria</td>\n",
       "      <td>32.937</td>\n",
       "      <td>112.02W</td>\n",
       "      <td>32.95N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146171</th>\n",
       "      <td>Mesa</td>\n",
       "      <td>32.765</td>\n",
       "      <td>112.02W</td>\n",
       "      <td>32.95N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146172</th>\n",
       "      <td>Mesa</td>\n",
       "      <td>32.429</td>\n",
       "      <td>112.02W</td>\n",
       "      <td>32.95N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146173</th>\n",
       "      <td>Glendale</td>\n",
       "      <td>32.656</td>\n",
       "      <td>112.02W</td>\n",
       "      <td>32.95N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>146174 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            City  AverageTemperature Longitude Latitude\n",
       "0         Jiroft              32.044    57.27E   28.13N\n",
       "1          Mecca              31.843    40.67E   21.70N\n",
       "2          Mecca              32.065    40.67E   21.70N\n",
       "3          Rabak              33.028    32.20E   13.66N\n",
       "4       Damaturu              31.955    11.51E   12.05N\n",
       "...          ...                 ...       ...      ...\n",
       "146169     Tempe              32.086   112.02W   32.95N\n",
       "146170    Peoria              32.937   112.02W   32.95N\n",
       "146171      Mesa              32.765   112.02W   32.95N\n",
       "146172      Mesa              32.429   112.02W   32.95N\n",
       "146173  Glendale              32.656   112.02W   32.95N\n",
       "\n",
       "[146174 rows x 4 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT City, AverageTemperature, Longitude, Latitude\n",
    "FROM kaggle_modeled.City_Beam_DF\n",
    "WHERE AverageTemperature > (\n",
    "    SELECT MAX(t.AverageTemperature)\n",
    "    FROM kaggle_modeled.Country_Beam_DF as t\n",
    "    WHERE t.Country = \"India\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following creates a view of one of the four queries above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE OR REPLACE VIEW kaggle_modeled.v_Higher_Uncertainty_than_Texas AS\n",
    "    SELECT q.dt as Date,  \n",
    "    q.State as State, \n",
    "    MAX(q.AverageTemperature) as StateTemp, \n",
    "    MAX(r.LandAverageTemperature) as GlobalTemp \n",
    "    FROM `electric-spark-266716.kaggle_modeled.State` as q\n",
    "    JOIN `electric-spark-266716.kaggle_modeled.Date` as r\n",
    "    ON q.dt = r.dt \n",
    "    WHERE AverageTemperatureUncertainty >       \n",
    "        (SELECT MAX(s.AverageTemperatureUncertainty)\n",
    "         FROM `electric-spark-266716.kaggle_modeled.State` as s\n",
    "         where s.State = \"Texas\")\n",
    "    GROUP BY Date, State\n",
    "    ORDER BY Date, State\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}