kaggle_joins.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Example Join Queries**\n",
    "The following queries are examples of joins on the raw Global Surface Temperature Data and for the Modeled Global Surface Temperature Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Raw Global Surface Temperature Data Queries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query finds the dates on which state and country temperature data are available through an inner join on the date attribute between Global_Land_Temperatures_by_State and Global_Land_Temperatures_by_Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>CountryTemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Qinghai</td>\n",
       "      <td>0.314</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Sichuan</td>\n",
       "      <td>9.969</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Xizang</td>\n",
       "      <td>-1.697</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Yunnan</td>\n",
       "      <td>17.927</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Andaman And Nicobar</td>\n",
       "      <td>28.824</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Andhra Pradesh</td>\n",
       "      <td>30.899</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>14.362</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Assam</td>\n",
       "      <td>22.882</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Bihar</td>\n",
       "      <td>28.134</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Chandigarh</td>\n",
       "      <td>15.916</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Chhattisgarh</td>\n",
       "      <td>30.424</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Dadra And Nagar Haveli</td>\n",
       "      <td>29.633</td>\n",
       "      <td>9.478</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date                   State  StateTemp  CountryTemp\n",
       "0   1818-04-01                 Qinghai      0.314        9.478\n",
       "1   1818-04-01                 Sichuan      9.969        9.478\n",
       "2   1818-04-01                  Xizang     -1.697        9.478\n",
       "3   1818-04-01                  Yunnan     17.927        9.478\n",
       "4   1818-04-01     Andaman And Nicobar     28.824        9.478\n",
       "5   1818-04-01          Andhra Pradesh     30.899        9.478\n",
       "6   1818-04-01       Arunachal Pradesh     14.362        9.478\n",
       "7   1818-04-01                   Assam     22.882        9.478\n",
       "8   1818-04-01                   Bihar     28.134        9.478\n",
       "9   1818-04-01              Chandigarh     15.916        9.478\n",
       "10  1818-04-01            Chhattisgarh     30.424        9.478\n",
       "11  1818-04-01  Dadra And Nagar Haveli     29.633        9.478"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Global_Land_Temperatures_by_State.dt as Date, \n",
    "Global_Land_Temperatures_by_State.State as State,\n",
    "Global_Land_Temperatures_by_State.AverageTemperature as StateTemp,\n",
    "Global_Land_Temperatures_by_Country.AverageTemperature as CountryTemp\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_State\n",
    "JOIN kaggle_staging.Global_Land_Temperatures_by_Country\n",
    "ON Global_Land_Temperatures_by_State.dt = Global_Land_Temperatures_by_Country.dt\n",
    "WHERE Global_Land_Temperatures_by_Country.AverageTemperature IS NOT NULL\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query joins the tables Global_Land_Temperatures_by_City and Global_Land_Temperatures_by_Country to yield all cities' Average Temperature Uncertainty less than 1 and the affiliated Average Tempearture Uncertainty of the country. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.616</td>\n",
       "      <td>0.250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        City    Country  CityTempUncertainty  CountryTempUncertainty\n",
       "0   Adelaide  Australia                0.616                     NaN\n",
       "1   Adelaide  Australia                0.616                     NaN\n",
       "2   Adelaide  Australia                0.616                     NaN\n",
       "3   Adelaide  Australia                0.616                     NaN\n",
       "4   Adelaide  Australia                0.616                     NaN\n",
       "5   Adelaide  Australia                0.616                   0.125\n",
       "6   Adelaide  Australia                0.616                   0.125\n",
       "7   Adelaide  Australia                0.616                   0.125\n",
       "8   Adelaide  Australia                0.616                   0.125\n",
       "9   Adelaide  Australia                0.616                   0.125\n",
       "10  Adelaide  Australia                0.616                   0.125\n",
       "11  Adelaide  Australia                0.616                   0.250"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Global_Land_Temperatures_by_City.City as City,\n",
    "Global_Land_Temperatures_by_Country.Country as Country,\n",
    "Global_Land_Temperatures_by_City.AverageTemperatureUncertainty as CityTempUncertainty,\n",
    "Global_Land_Temperatures_by_Country.AverageTemperatureUncertainty as CountryTempUncertainty,\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_City\n",
    "LEFT JOIN kaggle_staging.Global_Land_Temperatures_by_Country\n",
    "ON Global_Land_Temperatures_by_City.Country = Global_Land_Temperatures_by_Country.Country\n",
    "WHERE Global_Land_Temperatures_by_City.AverageTemperatureUncertainty < 1 \n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query joins the table with recorded Land Temperatures of Major Cities with Land Temperatures by Country to yield a sorted table in descending order of the hottest cities paired with the average country temperature recorded on the same date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>Baghdad</td>\n",
       "      <td>38.283</td>\n",
       "      <td>37.401</td>\n",
       "      <td>Iraq</td>\n",
       "      <td>2000-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Baghdad</td>\n",
       "      <td>37.899</td>\n",
       "      <td>37.165</td>\n",
       "      <td>Iraq</td>\n",
       "      <td>2010-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Baghdad</td>\n",
       "      <td>37.870</td>\n",
       "      <td>36.810</td>\n",
       "      <td>Iraq</td>\n",
       "      <td>2010-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.859</td>\n",
       "      <td>36.495</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2012-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.755</td>\n",
       "      <td>36.066</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>1998-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.732</td>\n",
       "      <td>36.471</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2000-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.603</td>\n",
       "      <td>36.182</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2010-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.551</td>\n",
       "      <td>36.357</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2010-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.456</td>\n",
       "      <td>35.868</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2000-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Baghdad</td>\n",
       "      <td>37.371</td>\n",
       "      <td>36.398</td>\n",
       "      <td>Iraq</td>\n",
       "      <td>2012-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.290</td>\n",
       "      <td>35.800</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2001-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Riyadh</td>\n",
       "      <td>37.200</td>\n",
       "      <td>35.924</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>2002-07-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       City  CityTemp  CountryTemp       Country RecordingDate\n",
       "0   Baghdad    38.283       37.401          Iraq    2000-07-01\n",
       "1   Baghdad    37.899       37.165          Iraq    2010-08-01\n",
       "2   Baghdad    37.870       36.810          Iraq    2010-07-01\n",
       "3    Riyadh    37.859       36.495  Saudi Arabia    2012-07-01\n",
       "4    Riyadh    37.755       36.066  Saudi Arabia    1998-08-01\n",
       "5    Riyadh    37.732       36.471  Saudi Arabia    2000-07-01\n",
       "6    Riyadh    37.603       36.182  Saudi Arabia    2010-07-01\n",
       "7    Riyadh    37.551       36.357  Saudi Arabia    2010-08-01\n",
       "8    Riyadh    37.456       35.868  Saudi Arabia    2000-08-01\n",
       "9   Baghdad    37.371       36.398          Iraq    2012-07-01\n",
       "10   Riyadh    37.290       35.800  Saudi Arabia    2001-08-01\n",
       "11   Riyadh    37.200       35.924  Saudi Arabia    2002-07-01"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Global_Land_Temperatures_by_Major_City.City, \n",
    "Global_Land_Temperatures_by_Major_City.AverageTemperature as CityTemp, \n",
    "Global_Land_Temperatures_by_Country.AverageTemperature as CountryTemp,\n",
    "Global_Land_Temperatures_by_Country.Country,\n",
    "Global_Land_Temperatures_by_Major_City.dt as RecordingDate\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Major_City\n",
    "LEFT JOIN kaggle_staging.Global_Land_Temperatures_by_Country\n",
    "ON Global_Land_Temperatures_by_Country.dt = Global_Land_Temperatures_by_Major_City.dt and Global_Land_Temperatures_by_Country.Country = Global_Land_Temperatures_by_Major_City.Country\n",
    "WHERE Global_Land_Temperatures_by_Country.AverageTemperature IS NOT NULL\n",
    "ORDER BY CityTemp DESC\n",
    "Limit 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query joins Major Cities table with the City data to determine which of the cities were then classified as major cities. The data is also joined on date and the values of temperature from both data sets is shown to confirm the lack of discrepency between the two tables. Finally the query is ordered by temperature, returning the coldest major cities."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>MajorCity_foundin_AllCities</th>\n",
       "      <th>MajorCity_Data</th>\n",
       "      <th>City_Data</th>\n",
       "      <th>RecordingDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-26.772</td>\n",
       "      <td>-26.772</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-25.494</td>\n",
       "      <td>-25.494</td>\n",
       "      <td>1915-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-24.872</td>\n",
       "      <td>-24.872</td>\n",
       "      <td>1861-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-24.218</td>\n",
       "      <td>-24.218</td>\n",
       "      <td>1872-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-23.922</td>\n",
       "      <td>-23.922</td>\n",
       "      <td>1977-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-23.812</td>\n",
       "      <td>-23.812</td>\n",
       "      <td>1889-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-23.495</td>\n",
       "      <td>-23.495</td>\n",
       "      <td>2001-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-23.362</td>\n",
       "      <td>-23.362</td>\n",
       "      <td>1841-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Changchun</td>\n",
       "      <td>-23.272</td>\n",
       "      <td>-23.272</td>\n",
       "      <td>1922-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-22.929</td>\n",
       "      <td>-22.929</td>\n",
       "      <td>1931-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-22.763</td>\n",
       "      <td>-22.763</td>\n",
       "      <td>1838-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Harbin</td>\n",
       "      <td>-22.752</td>\n",
       "      <td>-22.752</td>\n",
       "      <td>1919-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   MajorCity_foundin_AllCities  MajorCity_Data  City_Data RecordingDate\n",
       "0                       Harbin         -26.772    -26.772    1922-01-01\n",
       "1                       Harbin         -25.494    -25.494    1915-01-01\n",
       "2                       Harbin         -24.872    -24.872    1861-01-01\n",
       "3                       Harbin         -24.218    -24.218    1872-01-01\n",
       "4                       Harbin         -23.922    -23.922    1977-01-01\n",
       "5                       Harbin         -23.812    -23.812    1889-01-01\n",
       "6                       Harbin         -23.495    -23.495    2001-01-01\n",
       "7                       Harbin         -23.362    -23.362    1841-01-01\n",
       "8                    Changchun         -23.272    -23.272    1922-01-01\n",
       "9                       Harbin         -22.929    -22.929    1931-01-01\n",
       "10                      Harbin         -22.763    -22.763    1838-01-01\n",
       "11                      Harbin         -22.752    -22.752    1919-01-01"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Global_Land_Temperatures_by_City.City as MajorCity_foundin_AllCities,\n",
    "Global_Land_Temperatures_by_Major_City.AverageTemperature as MajorCity_Data,\n",
    "Global_Land_Temperatures_by_City.AverageTemperature as City_Data,\n",
    "Global_Land_Temperatures_by_Major_City.dt as RecordingDate\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Major_City\n",
    "INNER JOIN kaggle_staging.Global_Land_Temperatures_by_City\n",
    "ON Global_Land_Temperatures_by_Major_City.City = Global_Land_Temperatures_by_City.City and Global_Land_Temperatures_by_Major_City.dt = Global_Land_Temperatures_by_City.dt \n",
    "WHERE Global_Land_Temperatures_by_Major_City.AverageTemperature is not NULL\n",
    "ORDER BY Global_Land_Temperatures_by_Major_City.AverageTemperature\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query finds all dates with Country and Global data and yields the Country temperature compared to the Maximum Land temperature for that day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>Country</th>\n",
       "      <th>CountryTemp</th>\n",
       "      <th>LandMaxTemperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1818-04-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>9.478</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1949-10-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>8.773</td>\n",
       "      <td>15.439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1966-04-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>7.564</td>\n",
       "      <td>13.950999999999999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1975-12-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>-5.090</td>\n",
       "      <td>9.079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1978-03-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>1.804</td>\n",
       "      <td>11.344000000000001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1979-11-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>0.206</td>\n",
       "      <td>11.807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1980-05-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>14.873</td>\n",
       "      <td>17.713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1983-09-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>15.847</td>\n",
       "      <td>18.412000000000006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1985-03-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>0.431</td>\n",
       "      <td>11.194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1986-10-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>8.525</td>\n",
       "      <td>14.803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1987-10-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>7.982</td>\n",
       "      <td>15.136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>Asia</td>\n",
       "      <td>-5.494</td>\n",
       "      <td>9.661</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Country  CountryTemp  LandMaxTemperature\n",
       "0   1818-04-01    Asia        9.478                None\n",
       "1   1949-10-01    Asia        8.773              15.439\n",
       "2   1966-04-01    Asia        7.564  13.950999999999999\n",
       "3   1975-12-01    Asia       -5.090               9.079\n",
       "4   1978-03-01    Asia        1.804  11.344000000000001\n",
       "5   1979-11-01    Asia        0.206              11.807\n",
       "6   1980-05-01    Asia       14.873              17.713\n",
       "7   1983-09-01    Asia       15.847  18.412000000000006\n",
       "8   1985-03-01    Asia        0.431              11.194\n",
       "9   1986-10-01    Asia        8.525              14.803\n",
       "10  1987-10-01    Asia        7.982              15.136\n",
       "11  2009-12-01    Asia       -5.494               9.661"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Global_Land_Temperatures_by_Country.dt as Date, \n",
    "Global_Land_Temperatures_by_Country.Country,\n",
    "Global_Land_Temperatures_by_Country.AverageTemperature as CountryTemp,\n",
    "Global_Temperatures.LandMaxTemperature,\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_Country\n",
    "JOIN kaggle_staging.Global_Temperatures\n",
    "ON Global_Land_Temperatures_by_Country.dt = Global_Temperatures.dt\n",
    "WHERE Global_Land_Temperatures_by_Country.AverageTemperature IS NOT NULL\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This query finds all dates with Global and State data to compare the State temperature with the Global Average Temperature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "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>1841-01-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>0.581</td>\n",
       "      <td>0.649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1841-02-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>2.716</td>\n",
       "      <td>2.468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1841-03-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>6.871</td>\n",
       "      <td>3.307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1841-04-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>13.748</td>\n",
       "      <td>6.770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1841-05-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>19.662</td>\n",
       "      <td>10.764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1841-06-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>23.152</td>\n",
       "      <td>13.062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1841-07-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>27.278</td>\n",
       "      <td>14.552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1841-08-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>26.070</td>\n",
       "      <td>13.708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1841-09-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>21.177</td>\n",
       "      <td>10.921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1841-10-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>15.574</td>\n",
       "      <td>8.765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1841-11-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>9.501</td>\n",
       "      <td>4.744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1841-12-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>3.529</td>\n",
       "      <td>2.548</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date  State  StateTemp  GlobalTemp\n",
       "0   1841-01-01  Anhui      0.581       0.649\n",
       "1   1841-02-01  Anhui      2.716       2.468\n",
       "2   1841-03-01  Anhui      6.871       3.307\n",
       "3   1841-04-01  Anhui     13.748       6.770\n",
       "4   1841-05-01  Anhui     19.662      10.764\n",
       "5   1841-06-01  Anhui     23.152      13.062\n",
       "6   1841-07-01  Anhui     27.278      14.552\n",
       "7   1841-08-01  Anhui     26.070      13.708\n",
       "8   1841-09-01  Anhui     21.177      10.921\n",
       "9   1841-10-01  Anhui     15.574       8.765\n",
       "10  1841-11-01  Anhui      9.501       4.744\n",
       "11  1841-12-01  Anhui      3.529       2.548"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Global_Land_Temperatures_by_State.dt as Date, \n",
    "Global_Land_Temperatures_by_State.State as State,\n",
    "Global_Land_Temperatures_by_State.AverageTemperature as StateTemp,\n",
    "Global_Temperatures.LandAverageTemperature as GlobalTemp\n",
    "FROM kaggle_staging.Global_Land_Temperatures_by_State\n",
    "JOIN kaggle_staging.Global_Temperatures\n",
    "ON Global_Land_Temperatures_by_State.dt = Global_Temperatures.dt\n",
    "WHERE Global_Land_Temperatures_by_State.AverageTemperature IS NOT NULL\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Modeled Global Surface Temperature Data Queries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This updated query for the modeled tables finds the dates on which state and country temperature data are available through an inner join on the date attribute between State entity table and Country entity table"
   ]
  },
  {
   "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>Date</th>\n",
       "      <th>State</th>\n",
       "      <th>StateTemp</th>\n",
       "      <th>CountryTemp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Yukon</td>\n",
       "      <td>9.161</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Alberta</td>\n",
       "      <td>13.286</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Ontario</td>\n",
       "      <td>13.484</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Manitoba</td>\n",
       "      <td>12.330</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Nova Scotia</td>\n",
       "      <td>13.061</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Saskatchewan</td>\n",
       "      <td>14.169</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>New Brunswick</td>\n",
       "      <td>14.196</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>British Columbia</td>\n",
       "      <td>10.064</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Prince Edward Island</td>\n",
       "      <td>13.700</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Northwest Territories</td>\n",
       "      <td>8.584</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1860-06-01</td>\n",
       "      <td>Newfoundland And Labrador</td>\n",
       "      <td>8.467</td>\n",
       "      <td>9.270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1796-03-01</td>\n",
       "      <td>Goa</td>\n",
       "      <td>26.088</td>\n",
       "      <td>22.319</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date                      State  StateTemp  CountryTemp\n",
       "0   1860-06-01                      Yukon      9.161        9.270\n",
       "1   1860-06-01                    Alberta     13.286        9.270\n",
       "2   1860-06-01                    Ontario     13.484        9.270\n",
       "3   1860-06-01                   Manitoba     12.330        9.270\n",
       "4   1860-06-01                Nova Scotia     13.061        9.270\n",
       "5   1860-06-01               Saskatchewan     14.169        9.270\n",
       "6   1860-06-01              New Brunswick     14.196        9.270\n",
       "7   1860-06-01           British Columbia     10.064        9.270\n",
       "8   1860-06-01       Prince Edward Island     13.700        9.270\n",
       "9   1860-06-01      Northwest Territories      8.584        9.270\n",
       "10  1860-06-01  Newfoundland And Labrador      8.467        9.270\n",
       "11  1796-03-01                        Goa     26.088       22.319"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT State.dt as Date, \n",
    "State.State as State,\n",
    "State.AverageTemperature as StateTemp,\n",
    "Country.AverageTemperature as CountryTemp\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",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This updated query joins the modeled entity tables, City and Country, to yield all cities' Average Temperature Uncertainty less than 1 and the affiliated Average Tempearture Uncertainty of the country. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Likasi</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Tampere</td>\n",
       "      <td>Finland</td>\n",
       "      <td>0.258</td>\n",
       "      <td>0.223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Butembo</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.703</td>\n",
       "      <td>0.223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Canberra</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.158</td>\n",
       "      <td>0.187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Juliaca</td>\n",
       "      <td>Peru</td>\n",
       "      <td>0.242</td>\n",
       "      <td>0.188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kisangani</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.988</td>\n",
       "      <td>0.755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Kisangani</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.793</td>\n",
       "      <td>0.604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Turku</td>\n",
       "      <td>Finland</td>\n",
       "      <td>0.623</td>\n",
       "      <td>0.957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.298</td>\n",
       "      <td>0.190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Toamasina</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>0.988</td>\n",
       "      <td>0.756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Nampula</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>0.249</td>\n",
       "      <td>0.308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Jamame</td>\n",
       "      <td>Somalia</td>\n",
       "      <td>0.859</td>\n",
       "      <td>1.015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         City                             Country  CityTempUncertainty  \\\n",
       "0      Likasi  Congo (Democratic Republic Of The)                0.500   \n",
       "1     Tampere                             Finland                0.258   \n",
       "2     Butembo  Congo (Democratic Republic Of The)                0.703   \n",
       "3    Canberra                           Australia                0.158   \n",
       "4     Juliaca                                Peru                0.242   \n",
       "5   Kisangani  Congo (Democratic Republic Of The)                0.988   \n",
       "6   Kisangani  Congo (Democratic Republic Of The)                0.793   \n",
       "7       Turku                             Finland                0.623   \n",
       "8    Adelaide                           Australia                0.298   \n",
       "9   Toamasina                          Madagascar                0.988   \n",
       "10    Nampula                          Mozambique                0.249   \n",
       "11     Jamame                             Somalia                0.859   \n",
       "\n",
       "    CountryTempUncertainty  \n",
       "0                    0.325  \n",
       "1                    0.223  \n",
       "2                    0.223  \n",
       "3                    0.187  \n",
       "4                    0.188  \n",
       "5                    0.755  \n",
       "6                    0.604  \n",
       "7                    0.957  \n",
       "8                    0.190  \n",
       "9                    0.756  \n",
       "10                   0.308  \n",
       "11                   1.015  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT City.City as City,\n",
    "Country.Country as Country,\n",
    "City.AverageTemperatureUncertainty as CityTempUncertainty,\n",
    "Country.AverageTemperatureUncertainty as CountryTempUncertainty,\n",
    "FROM kaggle_modeled.City\n",
    "LEFT JOIN kaggle_modeled.Country\n",
    "ON City.Country = Country.Country and City.dt = Country.dt\n",
    "WHERE City.AverageTemperatureUncertainty < 1 \n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This updated query joins the entiry tables of City and Country on date and country to yield a sorted table in descending order of the hottest cities paired with the average country temperature recorded on the same date."
   ]
  },
  {
   "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>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>Masjed E Soleyman</td>\n",
       "      <td>39.156</td>\n",
       "      <td>31.459</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2013-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ahvaz</td>\n",
       "      <td>39.156</td>\n",
       "      <td>31.459</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2013-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ahvaz</td>\n",
       "      <td>39.153</td>\n",
       "      <td>31.140</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2012-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Masjed E Soleyman</td>\n",
       "      <td>39.153</td>\n",
       "      <td>31.140</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2012-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Masjed E Soleyman</td>\n",
       "      <td>38.964</td>\n",
       "      <td>31.651</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2010-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Ahvaz</td>\n",
       "      <td>38.964</td>\n",
       "      <td>31.651</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2010-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Ahvaz</td>\n",
       "      <td>38.899</td>\n",
       "      <td>30.022</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2000-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Masjed E Soleyman</td>\n",
       "      <td>38.899</td>\n",
       "      <td>30.022</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2000-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Ahvaz</td>\n",
       "      <td>38.793</td>\n",
       "      <td>31.034</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2011-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Masjed E Soleyman</td>\n",
       "      <td>38.793</td>\n",
       "      <td>31.034</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2011-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Masjed E Soleyman</td>\n",
       "      <td>38.630</td>\n",
       "      <td>30.772</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2001-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Ahvaz</td>\n",
       "      <td>38.630</td>\n",
       "      <td>30.772</td>\n",
       "      <td>Iran</td>\n",
       "      <td>2001-07-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 City  CityTemp  CountryTemp Country RecordingDate\n",
       "0   Masjed E Soleyman    39.156       31.459    Iran    2013-07-01\n",
       "1               Ahvaz    39.156       31.459    Iran    2013-07-01\n",
       "2               Ahvaz    39.153       31.140    Iran    2012-07-01\n",
       "3   Masjed E Soleyman    39.153       31.140    Iran    2012-07-01\n",
       "4   Masjed E Soleyman    38.964       31.651    Iran    2010-07-01\n",
       "5               Ahvaz    38.964       31.651    Iran    2010-07-01\n",
       "6               Ahvaz    38.899       30.022    Iran    2000-07-01\n",
       "7   Masjed E Soleyman    38.899       30.022    Iran    2000-07-01\n",
       "8               Ahvaz    38.793       31.034    Iran    2011-07-01\n",
       "9   Masjed E Soleyman    38.793       31.034    Iran    2011-07-01\n",
       "10  Masjed E Soleyman    38.630       30.772    Iran    2001-07-01\n",
       "11              Ahvaz    38.630       30.772    Iran    2001-07-01"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT City.City, \n",
    "City.AverageTemperature as CityTemp, \n",
    "Country.AverageTemperature as CountryTemp,\n",
    "Country.Country,\n",
    "City.dt as RecordingDate\n",
    "FROM kaggle_modeled.City\n",
    "LEFT JOIN kaggle_modeled.Country\n",
    "ON Country.dt = City.dt and Country.Country = City.Country\n",
    "WHERE Country.AverageTemperature IS NOT NULL\n",
    "ORDER BY CityTemp DESC\n",
    "Limit 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Note- This is a completely new join as the previous join refrenced a staging table which did not translate to a modeled entity table\n",
    "#### This query joins on date and country to show the average temperature uncertainty of each city and the country it is in at distinct dates"
   ]
  },
  {
   "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>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>AvgCityTempUncertainty</th>\n",
       "      <th>AvgCountryTempUncertainty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1991-07-01</td>\n",
       "      <td>Likasi</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1942-09-01</td>\n",
       "      <td>Tampere</td>\n",
       "      <td>Finland</td>\n",
       "      <td>0.258</td>\n",
       "      <td>0.223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1832-12-01</td>\n",
       "      <td>Helsinki</td>\n",
       "      <td>Finland</td>\n",
       "      <td>4.494</td>\n",
       "      <td>4.369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1887-03-01</td>\n",
       "      <td>Harare</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>1.231</td>\n",
       "      <td>1.218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1960-12-01</td>\n",
       "      <td>Butembo</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.703</td>\n",
       "      <td>0.223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1845-02-01</td>\n",
       "      <td>Oulu</td>\n",
       "      <td>Finland</td>\n",
       "      <td>3.227</td>\n",
       "      <td>3.370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1998-07-01</td>\n",
       "      <td>Canberra</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.158</td>\n",
       "      <td>0.187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1993-01-01</td>\n",
       "      <td>Juliaca</td>\n",
       "      <td>Peru</td>\n",
       "      <td>0.242</td>\n",
       "      <td>0.188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1924-12-01</td>\n",
       "      <td>Kisangani</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.988</td>\n",
       "      <td>0.755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1896-06-01</td>\n",
       "      <td>Kisangani</td>\n",
       "      <td>Congo (Democratic Republic Of The)</td>\n",
       "      <td>0.793</td>\n",
       "      <td>0.604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1860-07-01</td>\n",
       "      <td>Turku</td>\n",
       "      <td>Finland</td>\n",
       "      <td>0.623</td>\n",
       "      <td>0.957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1984-12-01</td>\n",
       "      <td>Adelaide</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0.298</td>\n",
       "      <td>0.190</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date       City                             Country  \\\n",
       "0   1991-07-01     Likasi  Congo (Democratic Republic Of The)   \n",
       "1   1942-09-01    Tampere                             Finland   \n",
       "2   1832-12-01   Helsinki                             Finland   \n",
       "3   1887-03-01     Harare                            Zimbabwe   \n",
       "4   1960-12-01    Butembo  Congo (Democratic Republic Of The)   \n",
       "5   1845-02-01       Oulu                             Finland   \n",
       "6   1998-07-01   Canberra                           Australia   \n",
       "7   1993-01-01    Juliaca                                Peru   \n",
       "8   1924-12-01  Kisangani  Congo (Democratic Republic Of The)   \n",
       "9   1896-06-01  Kisangani  Congo (Democratic Republic Of The)   \n",
       "10  1860-07-01      Turku                             Finland   \n",
       "11  1984-12-01   Adelaide                           Australia   \n",
       "\n",
       "    AvgCityTempUncertainty  AvgCountryTempUncertainty  \n",
       "0                    0.500                      0.325  \n",
       "1                    0.258                      0.223  \n",
       "2                    4.494                      4.369  \n",
       "3                    1.231                      1.218  \n",
       "4                    0.703                      0.223  \n",
       "5                    3.227                      3.370  \n",
       "6                    0.158                      0.187  \n",
       "7                    0.242                      0.188  \n",
       "8                    0.988                      0.755  \n",
       "9                    0.793                      0.604  \n",
       "10                   0.623                      0.957  \n",
       "11                   0.298                      0.190  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Country.dt as Date, \n",
    "City.City,\n",
    "Country.Country,\n",
    "City.AverageTemperatureUncertainty as AvgCityTempUncertainty,\n",
    "Country.AverageTemperatureUncertainty as AvgCountryTempUncertainty\n",
    "FROM kaggle_modeled.Country\n",
    "JOIN kaggle_modeled.City\n",
    "ON Country.dt = City.dt and City.Country = Country.Country\n",
    "WHERE Country.AverageTemperature IS NOT NULL\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This update query joins on all dates within the entity tables Country and Date and yields the Country temperature compared to the Maximum Land temperature for that date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Country</th>\n",
       "      <th>CountryTemp</th>\n",
       "      <th>LandMaxTemperature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1836-08-01</td>\n",
       "      <td>Burma</td>\n",
       "      <td>24.997</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1828-10-01</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>23.275</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1834-05-01</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>27.213</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1842-03-01</td>\n",
       "      <td>Italy</td>\n",
       "      <td>7.851</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1854-07-01</td>\n",
       "      <td>Libya</td>\n",
       "      <td>30.018</td>\n",
       "      <td>20.426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1753-05-01</td>\n",
       "      <td>Malta</td>\n",
       "      <td>19.472</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1832-08-01</td>\n",
       "      <td>Malta</td>\n",
       "      <td>25.471</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1891-05-01</td>\n",
       "      <td>Qatar</td>\n",
       "      <td>30.718</td>\n",
       "      <td>16.645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1857-02-01</td>\n",
       "      <td>Syria</td>\n",
       "      <td>6.304</td>\n",
       "      <td>9.602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1870-05-01</td>\n",
       "      <td>Syria</td>\n",
       "      <td>23.256</td>\n",
       "      <td>18.068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1880-09-01</td>\n",
       "      <td>Yemen</td>\n",
       "      <td>28.949</td>\n",
       "      <td>17.558</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1830-09-01</td>\n",
       "      <td>Bhutan</td>\n",
       "      <td>14.716</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date Country  CountryTemp LandMaxTemperature\n",
       "0   1836-08-01   Burma       24.997               None\n",
       "1   1828-10-01   Egypt       23.275               None\n",
       "2   1834-05-01   Egypt       27.213               None\n",
       "3   1842-03-01   Italy        7.851               None\n",
       "4   1854-07-01   Libya       30.018             20.426\n",
       "5   1753-05-01   Malta       19.472               None\n",
       "6   1832-08-01   Malta       25.471               None\n",
       "7   1891-05-01   Qatar       30.718             16.645\n",
       "8   1857-02-01   Syria        6.304              9.602\n",
       "9   1870-05-01   Syria       23.256             18.068\n",
       "10  1880-09-01   Yemen       28.949             17.558\n",
       "11  1830-09-01  Bhutan       14.716               None"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT Country.dt as Date, \n",
    "Country.Country,\n",
    "Country.AverageTemperature as CountryTemp,\n",
    "Date.LandMaxTemperature,\n",
    "FROM kaggle_modeled.Country\n",
    "JOIN kaggle_modeled.Date\n",
    "ON Country.dt = Date.dt\n",
    "WHERE Country.AverageTemperature IS NOT NULL\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This updated query joins on all dates within the entity  Global and State data to compare the State temperature with the Global Average Temperature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>1841-08-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>26.070</td>\n",
       "      <td>13.708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1843-11-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>9.477</td>\n",
       "      <td>4.947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1848-04-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>13.698</td>\n",
       "      <td>8.110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1859-03-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>8.234</td>\n",
       "      <td>5.023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1860-07-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>26.298</td>\n",
       "      <td>14.203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1864-05-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>19.342</td>\n",
       "      <td>10.956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1872-12-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>6.266</td>\n",
       "      <td>2.627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1874-04-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>15.558</td>\n",
       "      <td>8.231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1877-12-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>3.837</td>\n",
       "      <td>2.996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1880-08-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>25.884</td>\n",
       "      <td>13.807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1908-08-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>27.563</td>\n",
       "      <td>13.376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1910-08-01</td>\n",
       "      <td>Anhui</td>\n",
       "      <td>26.948</td>\n",
       "      <td>13.677</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date  State  StateTemp  GlobalTemp\n",
       "0   1841-08-01  Anhui     26.070      13.708\n",
       "1   1843-11-01  Anhui      9.477       4.947\n",
       "2   1848-04-01  Anhui     13.698       8.110\n",
       "3   1859-03-01  Anhui      8.234       5.023\n",
       "4   1860-07-01  Anhui     26.298      14.203\n",
       "5   1864-05-01  Anhui     19.342      10.956\n",
       "6   1872-12-01  Anhui      6.266       2.627\n",
       "7   1874-04-01  Anhui     15.558       8.231\n",
       "8   1877-12-01  Anhui      3.837       2.996\n",
       "9   1880-08-01  Anhui     25.884      13.807\n",
       "10  1908-08-01  Anhui     27.563      13.376\n",
       "11  1910-08-01  Anhui     26.948      13.677"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT State.dt as Date, \n",
    "State.State as State,\n",
    "State.AverageTemperature as StateTemp,\n",
    "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",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}