SQL_Climate_11_09_24.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "fYZNKoskJ9iM",
        "UzumW8-TeKGf",
        "l5VqTxRhdlqA",
        "qcpczllsYM_O",
        "9xiNKSelBmaJ"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/SurayaSumona/Masterschool-SQL-Practice/blob/main/SQL_Climate_11_09_24.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Climate Change Dataset\n",
        "* **Climate Change:** Earth Surface Temperature Data; Exploring global temperatures since 1750\n",
        "* **Source:**[Kaggle Climate Change: Earth Surface Temperature Data](https://www.kaggle.com/datasets/berkeleyearth/climate-change-earth-surface-temperature-data)\n",
        "\n",
        "\n",
        "\n"
      ],
      "metadata": {
        "id": "2yZF1n50wI2t"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Background\n",
        "\n",
        "The World Climate Committee (WCC) is examining the temperature data to understand better the climate conditions that are present before the Winter Olympics in Sochi and the preparation phase for the World Expo in Milan. The data is filtered up to September of 2013 and this session aims to identify patterns and anomalies that could have affected these significant international events."
      ],
      "metadata": {
        "id": "Zm-EaMTwWHzW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Part 0: Preparations"
      ],
      "metadata": {
        "id": "ahsgA58OyrOo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Installing and importing packages"
      ],
      "metadata": {
        "id": "z5drZAqLjEMz"
      }
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "b6v4jqWkWwla"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import sqlalchemy as sa"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Making a connection"
      ],
      "metadata": {
        "id": "WktDkrC1jVGx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "climate_url = 'postgresql://student:tAdJApZJw7X3C%40xs@ep-aged-math-14007156.us-east-2.aws.neon.tech/climate?sslmode=require'"
      ],
      "metadata": {
        "id": "6FVMSLAlW7H3"
      },
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Remember: Connecting with SQLAlchemy always works in two steps:\n",
        "\n",
        "1.   Create an engine\n",
        "2.   Make a connection"
      ],
      "metadata": {
        "id": "Dtzgxc0sPSEe"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "engine = sa.create_engine(climate_url)\n",
        "connection = engine.connect().execution_options(isolation_level=\"AUTOCOMMIT\")"
      ],
      "metadata": {
        "id": "ro4HsV-DaZIi"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Solving questions easy to medium"
      ],
      "metadata": {
        "id": "NgX4zytEjdPE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "##Q1. For Italy in August 2013, calculate the expected maximum and minimum temperatures by considering the temperature uncertainty.\n",
        "\"\"\"\n",
        "Hints:\n",
        "Filter for Italy and August 2013.\n",
        "Add and subtract uncertainty to the average temperature.\n",
        "Motivation: For the WCC, knowing the potential temperature range in Italy during the peak summer month provides an indication of the climatic conditions\n",
        "that the organizers of the World Expo might have considered in their early planning stages.\n",
        "\"\"\"\n",
        "query1 = \"\"\"\n",
        "SELECT\n",
        "\tavg_temp,\n",
        "\t(avg_temp + avg_temp_uncertainty) ExpectedMAXTemp,\n",
        "  (avg_temp - avg_temp_uncertainty) ExpectedMinTemp\n",
        "FROM temperatures_by_country\n",
        "WHERE country = 'Italy' AND EXTRACT(YEAR FROM dt)= 2013 AND EXTRACT(MONTH FROM dt)= 8\n",
        ";\n",
        "\"\"\"\n",
        "print(\"Result Query 1:\")\n",
        "display (pd.read_sql(sa.text(query1),connection))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 99
        },
        "id": "zB3RZbebADdc",
        "outputId": "3a35c767-adb5-4458-e356-e1e3385b3273"
      },
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Result Query 1:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "   avg_temp  expectedmaxtemp  expectedmintemp\n",
              "0    23.603           24.229           22.977"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b7b126bf-d6c8-4a74-91f5-be853228e9a1\" class=\"colab-df-container\">\n",
              "    <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>avg_temp</th>\n",
              "      <th>expectedmaxtemp</th>\n",
              "      <th>expectedmintemp</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>23.603</td>\n",
              "      <td>24.229</td>\n",
              "      <td>22.977</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b7b126bf-d6c8-4a74-91f5-be853228e9a1')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-b7b126bf-d6c8-4a74-91f5-be853228e9a1 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-b7b126bf-d6c8-4a74-91f5-be853228e9a1');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"display (pd\",\n  \"rows\": 1,\n  \"fields\": [\n    {\n      \"column\": \"avg_temp\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": null,\n        \"min\": 23.603,\n        \"max\": 23.603,\n        \"num_unique_values\": 1,\n        \"samples\": [\n          23.603\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"expectedmaxtemp\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": null,\n        \"min\": 24.229000000000003,\n        \"max\": 24.229000000000003,\n        \"num_unique_values\": 1,\n        \"samples\": [\n          24.229000000000003\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"expectedmintemp\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": null,\n        \"min\": 22.977,\n        \"max\": 22.977,\n        \"num_unique_values\": 1,\n        \"samples\": [\n          22.977\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "##Q2.For August 2013, classify countries into 'Hot', 'Moderate', and 'Cold' based on their average temperatures. Hot: >30, Moderate: between 20 and 30, Cold: Otherwise)\n",
        "\"\"\"\n",
        "Hints:\n",
        "Filter for August 2013.\n",
        "Use CASE WHEN.\n",
        "Motivation: This classification will help the WCC to understand how the global temperature was distributed in the month leading up to\n",
        "the final quarter of 2013, providing insights into the climatic conditions before the year of the Winter Olympics and World Expo preparations.\n",
        "\"\"\"\n",
        "query2 = \"\"\"\n",
        "SELECT\n",
        "\tcountry,\n",
        "  CASE WHEN avg_temp>30 THEN 'Hot'\n",
        "  \t\tWHEN avg_temp BETWEEN 20 AND 30 THEN 'Moderate'\n",
        "      ELSE 'Cold'\n",
        "  END AS classification\n",
        "FROM temperatures_by_country\n",
        "--WHERE DATE_TRUNC(\n",
        "WHERE EXTRACT(YEAR FROM dt)= 2013 AND EXTRACT(MONTH FROM dt)= 8;\n",
        "\"\"\"\n",
        "print(\"Result Query 2:\")\n",
        "display (pd.read_sql(sa.text(query2),connection))"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 442
        },
        "id": "tNczcbj9AL5S",
        "outputId": "0e7dafbd-7048-497b-822c-408e3624740b"
      },
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Result Query 2:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "                                country classification\n",
              "0                              Ethiopia       Moderate\n",
              "1                                Europe           Cold\n",
              "2     Falkland Islands (Islas Malvinas)           Cold\n",
              "3                         Faroe Islands           Cold\n",
              "4        Federated States Of Micronesia       Moderate\n",
              "..                                  ...            ...\n",
              "238                               China           Cold\n",
              "239                    Christmas Island       Moderate\n",
              "240                            Colombia       Moderate\n",
              "241                             Comoros       Moderate\n",
              "242  Congo (Democratic Republic Of The)       Moderate\n",
              "\n",
              "[243 rows x 2 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-5967f71a-f077-4491-82df-86b648b7e88b\" class=\"colab-df-container\">\n",
              "    <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>country</th>\n",
              "      <th>classification</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ethiopia</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Europe</td>\n",
              "      <td>Cold</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Falkland Islands (Islas Malvinas)</td>\n",
              "      <td>Cold</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Faroe Islands</td>\n",
              "      <td>Cold</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Federated States Of Micronesia</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>238</th>\n",
              "      <td>China</td>\n",
              "      <td>Cold</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>239</th>\n",
              "      <td>Christmas Island</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>240</th>\n",
              "      <td>Colombia</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>241</th>\n",
              "      <td>Comoros</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>242</th>\n",
              "      <td>Congo (Democratic Republic Of The)</td>\n",
              "      <td>Moderate</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>243 rows × 2 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5967f71a-f077-4491-82df-86b648b7e88b')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-5967f71a-f077-4491-82df-86b648b7e88b button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-5967f71a-f077-4491-82df-86b648b7e88b');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-cc3ea47d-ebe6-487f-823e-d77848baacf2\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-cc3ea47d-ebe6-487f-823e-d77848baacf2')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-cc3ea47d-ebe6-487f-823e-d77848baacf2 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"display (pd\",\n  \"rows\": 243,\n  \"fields\": [\n    {\n      \"column\": \"country\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 243,\n        \"samples\": [\n          \"Hong Kong\",\n          \"Finland\",\n          \"Belgium\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"classification\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"Moderate\",\n          \"Cold\",\n          \"Hot\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "##Q3.List the top 5 countries with the highest average temperatures in the summer of 2013(june, July, August)\n",
        "\"\"\"\n",
        "Hint:\n",
        "Filter for June, July, August of 2013 and exclude nulls from avg_temp column.\n",
        "Motivation: These results will shed light on which countries experienced the hottest summers before the Winter Olympics,\n",
        "which could influence the training conditions for athletes.\n",
        "\"\"\"\n",
        "query3 = \"\"\"\n",
        "SELECT country, ROUND(AVG(avg_temp)::numeric,2)\n",
        "FROM temperatures_by_country\n",
        "WHERE avg_temp IS NOT NULL AND EXTRACT(MONTH FROM dt) IN (6,7,8) AND EXTRACT(YEAR FROM dt)= 2013\n",
        "GROUP BY 1\n",
        "ORDER BY AVG(avg_temp) DESC\n",
        "LIMIT 5;\n",
        "\"\"\"\n",
        "print(\"Result Query 3:\")\n",
        "display (pd.read_sql(sa.text(query3),connection))"
      ],
      "metadata": {
        "id": "ftyTHNt0cMty",
        "outputId": "2e90e32e-42b9-4b69-bcef-b02cca8631a8",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 224
        }
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Result Query 3:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "                country  round\n",
              "0                Kuwait  36.88\n",
              "1                 Qatar  36.05\n",
              "2  United Arab Emirates  36.01\n",
              "3               Bahrain  35.72\n",
              "4          Saudi Arabia  34.95"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-e57fa246-e1b9-4ef3-a644-26f85e8c9985\" class=\"colab-df-container\">\n",
              "    <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>country</th>\n",
              "      <th>round</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Kuwait</td>\n",
              "      <td>36.88</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Qatar</td>\n",
              "      <td>36.05</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>United Arab Emirates</td>\n",
              "      <td>36.01</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Bahrain</td>\n",
              "      <td>35.72</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Saudi Arabia</td>\n",
              "      <td>34.95</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e57fa246-e1b9-4ef3-a644-26f85e8c9985')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-e57fa246-e1b9-4ef3-a644-26f85e8c9985 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-e57fa246-e1b9-4ef3-a644-26f85e8c9985');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-5be5266c-e157-4329-8958-24db5d0dec87\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-5be5266c-e157-4329-8958-24db5d0dec87')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-5be5266c-e157-4329-8958-24db5d0dec87 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"display (pd\",\n  \"rows\": 5,\n  \"fields\": [\n    {\n      \"column\": \"country\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 5,\n        \"samples\": [\n          \"Qatar\",\n          \"Saudi Arabia\",\n          \"United Arab Emirates\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"round\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.6941685674243683,\n        \"min\": 34.95,\n        \"max\": 36.88,\n        \"num_unique_values\": 5,\n        \"samples\": [\n          36.05,\n          34.95,\n          36.01\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#Q4.List countries that saw a difference of more than 10°C between their highest and lowest temperatures in the first eight months of 2013.\n",
        "\"\"\"\n",
        "Hints:\n",
        "Filter for dates up to August 2013.\n",
        "Use MAX and MIN functions.\n",
        "Use Having clause for >10°C swing.\n",
        "Motivation: Identifying such countries will give the WCC insights into which regions experienced significant temperature variations\n",
        "within the same year, which is crucial for understanding the potential impact on infrastructure and logistics planning for the upcoming events.\n",
        "\"\"\"\n",
        "query4 = \"\"\"\n",
        "SELECT\n",
        "\tcountry,\n",
        "  ROUND(MAX(avg_temp)::numeric,2) highest_tem,\n",
        "  ROUND(MIN(avg_temp)::numeric,2) lowest_temp,\n",
        "  ROUND((MAX(avg_temp)- MIN(avg_temp))::numeric,2) difference_temp\n",
        "FROM temperatures_by_country\n",
        "WHERE avg_temp IS NOT NULL AND EXTRACT(MONTH FROM dt) BETWEEN 1 AND 8\n",
        "AND EXTRACT(YEAR FROM dt)= 2013\n",
        "GROUP BY 1\n",
        "HAVING MAX(avg_temp)- MIN(avg_temp)>10;\n",
        "\"\"\"\n",
        "print(\"Result Query 4:\")\n",
        "display(pd.read_sql(sa.text(query4),connection))"
      ],
      "metadata": {
        "id": "DSgVTX5AI7HW",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 442
        },
        "outputId": "8ebd40a4-bd84-445b-e199-9b007fbeb0c5"
      },
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Result Query 4:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "            country  highest_tem  lowest_temp  difference_temp\n",
              "0       Afghanistan        28.21         2.24            25.96\n",
              "1           Albania        24.79         4.41            20.38\n",
              "2           Algeria        34.71        12.78            21.93\n",
              "3           Andorra        22.00         3.99            18.02\n",
              "4         Argentina        22.48         8.34            14.13\n",
              "..              ...          ...          ...              ...\n",
              "110         Uruguay        23.52        11.46            12.06\n",
              "111      Uzbekistan        28.44         0.35            28.09\n",
              "112  Western Sahara        28.56        17.46            11.10\n",
              "113           Yemen        32.33        21.57            10.76\n",
              "114           Åland        16.45        -4.78            21.23\n",
              "\n",
              "[115 rows x 4 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-3025b14c-2d68-4334-8b33-eb3667555429\" class=\"colab-df-container\">\n",
              "    <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>country</th>\n",
              "      <th>highest_tem</th>\n",
              "      <th>lowest_temp</th>\n",
              "      <th>difference_temp</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Afghanistan</td>\n",
              "      <td>28.21</td>\n",
              "      <td>2.24</td>\n",
              "      <td>25.96</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Albania</td>\n",
              "      <td>24.79</td>\n",
              "      <td>4.41</td>\n",
              "      <td>20.38</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Algeria</td>\n",
              "      <td>34.71</td>\n",
              "      <td>12.78</td>\n",
              "      <td>21.93</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Andorra</td>\n",
              "      <td>22.00</td>\n",
              "      <td>3.99</td>\n",
              "      <td>18.02</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>Argentina</td>\n",
              "      <td>22.48</td>\n",
              "      <td>8.34</td>\n",
              "      <td>14.13</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>110</th>\n",
              "      <td>Uruguay</td>\n",
              "      <td>23.52</td>\n",
              "      <td>11.46</td>\n",
              "      <td>12.06</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>111</th>\n",
              "      <td>Uzbekistan</td>\n",
              "      <td>28.44</td>\n",
              "      <td>0.35</td>\n",
              "      <td>28.09</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>112</th>\n",
              "      <td>Western Sahara</td>\n",
              "      <td>28.56</td>\n",
              "      <td>17.46</td>\n",
              "      <td>11.10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>113</th>\n",
              "      <td>Yemen</td>\n",
              "      <td>32.33</td>\n",
              "      <td>21.57</td>\n",
              "      <td>10.76</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>114</th>\n",
              "      <td>Åland</td>\n",
              "      <td>16.45</td>\n",
              "      <td>-4.78</td>\n",
              "      <td>21.23</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>115 rows × 4 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-3025b14c-2d68-4334-8b33-eb3667555429')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-3025b14c-2d68-4334-8b33-eb3667555429 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-3025b14c-2d68-4334-8b33-eb3667555429');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-b62c353f-a54c-4040-a8c1-c97da94ba710\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-b62c353f-a54c-4040-a8c1-c97da94ba710')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-b62c353f-a54c-4040-a8c1-c97da94ba710 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"display(pd\",\n  \"rows\": 115,\n  \"fields\": [\n    {\n      \"column\": \"country\",\n      \"properties\": {\n        \"dtype\": \"string\",\n        \"num_unique_values\": 115,\n        \"samples\": [\n          \"Pakistan\",\n          \"Argentina\",\n          \"India\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"highest_tem\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 7.075136294989472,\n        \"min\": -1.29,\n        \"max\": 38.23,\n        \"num_unique_values\": 112,\n        \"samples\": [\n          31.01,\n          21.09,\n          22.48\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"lowest_temp\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 10.6952616082808,\n        \"min\": -29.73,\n        \"max\": 21.57,\n        \"num_unique_values\": 111,\n        \"samples\": [\n          9.8,\n          17.55,\n          8.34\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"difference_temp\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 6.1274435471984345,\n        \"min\": 10.53,\n        \"max\": 41.58,\n        \"num_unique_values\": 111,\n        \"samples\": [\n          22.99,\n          19.37,\n          14.13\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Q5.Compare Russia's monthly temperatures for each month in 2013 with the same month in 2012 to check for warmer or cooler trends.\n",
        "\"\"\"\n",
        "Hints:\n",
        "Join the table on itself on the month values.\n",
        "Filter the country for Russia (in the join).\n",
        "Filter the year for 2013 and 2012 for related sides of the join.\n",
        "Motivation: The results would show whether Russia experienced warmer or cooler months in 2013 compared to 2012,\n",
        "indicating potential climate variability or trends that could inform climate-related planning and policy.\n",
        "\"\"\"\n",
        "query5 = \"\"\"\n",
        "WITH temp_russia AS (\n",
        "    SELECT\n",
        "        EXTRACT(YEAR FROM dt) AS year,\n",
        "        EXTRACT(MONTH FROM dt) AS month,\n",
        "        avg_temp,\n",
        "        country\n",
        "    FROM\n",
        "        temperatures_by_country\n",
        "    WHERE\n",
        "        country = 'Russia'\n",
        "        AND EXTRACT(YEAR FROM dt) IN (2012, 2013)\n",
        ")\n",
        "SELECT\n",
        "    t2013.month,\n",
        "    t2013.avg_temp AS temp_2013,\n",
        "    t2012.avg_temp AS temp_2012,\n",
        "    CASE\n",
        "        WHEN t2013.avg_temp > t2012.avg_temp THEN 'Warmer'\n",
        "        WHEN t2013.avg_temp < t2012.avg_temp THEN 'Cooler'\n",
        "        ELSE 'Same'\n",
        "    END AS trend\n",
        "FROM\n",
        "    temp_russia t2013\n",
        "JOIN\n",
        "    temp_russia t2012\n",
        "    ON t2013.month = t2012.month\n",
        "    AND t2013.year = 2013\n",
        "    AND t2012.year = 2012\n",
        "ORDER BY\n",
        "    t2013.month;\n",
        "\"\"\"\n",
        "print(\"Result Query 5:\")\n",
        "display(pd.read_sql(sa.text(query5),connection))"
      ],
      "metadata": {
        "id": "veocFTaXXSHT",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 349
        },
        "outputId": "ca40d86f-4693-4089-e7c7-83470bd0eda0"
      },
      "execution_count": 20,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Result Query 5:\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "   month  temp_2013  temp_2012   trend\n",
              "0    1.0    -25.530    -24.229  Cooler\n",
              "1    2.0    -22.429    -22.502  Warmer\n",
              "2    3.0    -16.670    -15.098  Cooler\n",
              "3    4.0     -2.986     -2.503  Cooler\n",
              "4    5.0      6.313      6.594  Cooler\n",
              "5    6.0     13.327     14.441  Cooler\n",
              "6    7.0     16.051     16.741  Cooler\n",
              "7    8.0     13.819     13.014  Warmer\n",
              "8    9.0        NaN      7.769    Same"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-81f36f02-1521-461f-964f-7a03ebc46a74\" class=\"colab-df-container\">\n",
              "    <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>month</th>\n",
              "      <th>temp_2013</th>\n",
              "      <th>temp_2012</th>\n",
              "      <th>trend</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1.0</td>\n",
              "      <td>-25.530</td>\n",
              "      <td>-24.229</td>\n",
              "      <td>Cooler</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>2.0</td>\n",
              "      <td>-22.429</td>\n",
              "      <td>-22.502</td>\n",
              "      <td>Warmer</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>3.0</td>\n",
              "      <td>-16.670</td>\n",
              "      <td>-15.098</td>\n",
              "      <td>Cooler</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4.0</td>\n",
              "      <td>-2.986</td>\n",
              "      <td>-2.503</td>\n",
              "      <td>Cooler</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>5.0</td>\n",
              "      <td>6.313</td>\n",
              "      <td>6.594</td>\n",
              "      <td>Cooler</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>6.0</td>\n",
              "      <td>13.327</td>\n",
              "      <td>14.441</td>\n",
              "      <td>Cooler</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>7.0</td>\n",
              "      <td>16.051</td>\n",
              "      <td>16.741</td>\n",
              "      <td>Cooler</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>8.0</td>\n",
              "      <td>13.819</td>\n",
              "      <td>13.014</td>\n",
              "      <td>Warmer</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>9.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7.769</td>\n",
              "      <td>Same</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-81f36f02-1521-461f-964f-7a03ebc46a74')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-81f36f02-1521-461f-964f-7a03ebc46a74 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-81f36f02-1521-461f-964f-7a03ebc46a74');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-9fc8f516-8f6f-4f37-8f20-a48d068b6c29\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-9fc8f516-8f6f-4f37-8f20-a48d068b6c29')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-9fc8f516-8f6f-4f37-8f20-a48d068b6c29 button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"display(pd\",\n  \"rows\": 9,\n  \"fields\": [\n    {\n      \"column\": \"month\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 2.7386127875258306,\n        \"min\": 1.0,\n        \"max\": 9.0,\n        \"num_unique_values\": 9,\n        \"samples\": [\n          8.0,\n          2.0,\n          6.0\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"temp_2013\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 17.18277550703029,\n        \"min\": -25.53,\n        \"max\": 16.051,\n        \"num_unique_values\": 8,\n        \"samples\": [\n          -22.429,\n          13.326999999999998,\n          -25.53\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"temp_2012\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 16.147375770625324,\n        \"min\": -24.229,\n        \"max\": 16.741,\n        \"num_unique_values\": 9,\n        \"samples\": [\n          13.014000000000001,\n          -22.502,\n          14.441\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"trend\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 3,\n        \"samples\": [\n          \"Cooler\",\n          \"Warmer\",\n          \"Same\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {}
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "##Q6. Identify pairs of countries that had, on average, similar temperatures (less than 1 degree difference) in the years from 2010 to 2013.\n",
        "\"\"\"\n",
        "Hints:\n",
        "Calculate country averages from 2010 to 2013 in a CTE.\n",
        "Join the CTE with itself.\n",
        "Use ABS for differences.\n",
        "Ensure comparison is between different countries.\n",
        "Filter for differences < 1°C.\n",
        "Motivation: Identifying countries with similar temperatures aids the WCC in analyzing climate conditions before the 2014\n",
        "Winter Olympics and the Milan Expo preparations, enhancing event strategy and revealing broader climate trends relevant to these global events.\n",
        "\"\"\"\n",
        "query6 = \"\"\"\n",
        "WITH country_avg_temp AS (\n",
        "    -- Calculate the average temperature for each country from 2010 to 2013\n",
        "    SELECT\n",
        "        country,\n",
        "        AVG(avg_temp) AS avg_temperature\n",
        "    FROM\n",
        "        temperatures_by_country\n",
        "    WHERE\n",
        "        EXTRACT(YEAR FROM dt) BETWEEN 2010 AND 2013\n",
        "    GROUP BY\n",
        "        country\n",
        ")\n",
        "SELECT\n",
        "    a.country AS country_1,\n",
        "    b.country AS country_2,\n",
        "    ROUND(a.avg_temperature:: numeric,2) AS avg_temp_country_1,\n",
        "    ROUND(b.avg_temperature:: numeric,2) AS avg_temp_country_2,\n",
        "    ROUND((ABS(a.avg_temperature - b.avg_temperature)):: numeric,2) AS temperature_difference\n",
        "FROM\n",
        "    country_avg_temp a\n",
        "JOIN\n",
        "    country_avg_temp b\n",
        "    ON a.country < b.country -- Ensure the comparison is between different countries and avoid duplicates\n",
        "WHERE\n",
        "    ABS(a.avg_temperature - b.avg_temperature) < 1 -- Filter for less than 1 degree difference\n",
        "ORDER BY\n",
        "    temperature_difference;\n",
        "\"\"\"\n",
        "print(\"Result Query 6\")\n",
        "display(pd.read_sql(sa.text(query6),connection))"
      ],
      "metadata": {
        "id": "gp9c1t-DHbj_",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 442
        },
        "outputId": "c48a4b08-4bbc-4bfe-e2bd-8fdea9e9821b"
      },
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Result Query 6\n"
          ]
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "                   country_1                country_2  avg_temp_country_1  \\\n",
              "0                   Ethiopia                    Tonga               24.11   \n",
              "1                    Algeria                  Vietnam               24.56   \n",
              "2                   Anguilla             Saint Martin               27.62   \n",
              "3                   Guernsey                   Jersey               11.54   \n",
              "4     British Virgin Islands                     Guam               27.36   \n",
              "...                      ...                      ...                 ...   \n",
              "3642       Equatorial Guinea                Nicaragua               25.65   \n",
              "3643                 Romania  United Kingdom (Europe)               10.22   \n",
              "3644                   Benin             Burkina Faso               28.07   \n",
              "3645                 Burundi                 Pakistan               20.80   \n",
              "3646                   Aruba            Guinea Bissau               28.68   \n",
              "\n",
              "      avg_temp_country_2  temperature_difference  \n",
              "0                  24.10                     0.0  \n",
              "1                  24.56                     0.0  \n",
              "2                  27.62                     0.0  \n",
              "3                  11.54                     0.0  \n",
              "4                  27.36                     0.0  \n",
              "...                  ...                     ...  \n",
              "3642               26.64                     1.0  \n",
              "3643                9.22                     1.0  \n",
              "3644               29.06                     1.0  \n",
              "3645               21.80                     1.0  \n",
              "3646               27.68                     1.0  \n",
              "\n",
              "[3647 rows x 5 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-f02ab816-7a75-437a-8f78-68fb431fb7d6\" class=\"colab-df-container\">\n",
              "    <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>country_1</th>\n",
              "      <th>country_2</th>\n",
              "      <th>avg_temp_country_1</th>\n",
              "      <th>avg_temp_country_2</th>\n",
              "      <th>temperature_difference</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>Ethiopia</td>\n",
              "      <td>Tonga</td>\n",
              "      <td>24.11</td>\n",
              "      <td>24.10</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>Algeria</td>\n",
              "      <td>Vietnam</td>\n",
              "      <td>24.56</td>\n",
              "      <td>24.56</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>Anguilla</td>\n",
              "      <td>Saint Martin</td>\n",
              "      <td>27.62</td>\n",
              "      <td>27.62</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>Guernsey</td>\n",
              "      <td>Jersey</td>\n",
              "      <td>11.54</td>\n",
              "      <td>11.54</td>\n",
              "      <td>0.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>British Virgin Islands</td>\n",
              "      <td>Guam</td>\n",
              "      <td>27.36</td>\n",
              "      <td>27.36</td>\n",
              "      <td>0.0</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>3642</th>\n",
              "      <td>Equatorial Guinea</td>\n",
              "      <td>Nicaragua</td>\n",
              "      <td>25.65</td>\n",
              "      <td>26.64</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3643</th>\n",
              "      <td>Romania</td>\n",
              "      <td>United Kingdom (Europe)</td>\n",
              "      <td>10.22</td>\n",
              "      <td>9.22</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3644</th>\n",
              "      <td>Benin</td>\n",
              "      <td>Burkina Faso</td>\n",
              "      <td>28.07</td>\n",
              "      <td>29.06</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3645</th>\n",
              "      <td>Burundi</td>\n",
              "      <td>Pakistan</td>\n",
              "      <td>20.80</td>\n",
              "      <td>21.80</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3646</th>\n",
              "      <td>Aruba</td>\n",
              "      <td>Guinea Bissau</td>\n",
              "      <td>28.68</td>\n",
              "      <td>27.68</td>\n",
              "      <td>1.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>3647 rows × 5 columns</p>\n",
              "</div>\n",
              "    <div class=\"colab-df-buttons\">\n",
              "\n",
              "  <div class=\"colab-df-container\">\n",
              "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-f02ab816-7a75-437a-8f78-68fb431fb7d6')\"\n",
              "            title=\"Convert this dataframe to an interactive table.\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\" viewBox=\"0 -960 960 960\">\n",
              "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
              "  </svg>\n",
              "    </button>\n",
              "\n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    .colab-df-buttons div {\n",
              "      margin-bottom: 4px;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "    <script>\n",
              "      const buttonEl =\n",
              "        document.querySelector('#df-f02ab816-7a75-437a-8f78-68fb431fb7d6 button.colab-df-convert');\n",
              "      buttonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "      async function convertToInteractive(key) {\n",
              "        const element = document.querySelector('#df-f02ab816-7a75-437a-8f78-68fb431fb7d6');\n",
              "        const dataTable =\n",
              "          await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                    [key], {});\n",
              "        if (!dataTable) return;\n",
              "\n",
              "        const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "          '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "          + ' to learn more about interactive tables.';\n",
              "        element.innerHTML = '';\n",
              "        dataTable['output_type'] = 'display_data';\n",
              "        await google.colab.output.renderOutput(dataTable, element);\n",
              "        const docLink = document.createElement('div');\n",
              "        docLink.innerHTML = docLinkHtml;\n",
              "        element.appendChild(docLink);\n",
              "      }\n",
              "    </script>\n",
              "  </div>\n",
              "\n",
              "\n",
              "<div id=\"df-be397270-aa2d-4c6f-809a-05c75d3d568b\">\n",
              "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-be397270-aa2d-4c6f-809a-05c75d3d568b')\"\n",
              "            title=\"Suggest charts\"\n",
              "            style=\"display:none;\">\n",
              "\n",
              "<svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "     width=\"24px\">\n",
              "    <g>\n",
              "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
              "    </g>\n",
              "</svg>\n",
              "  </button>\n",
              "\n",
              "<style>\n",
              "  .colab-df-quickchart {\n",
              "      --bg-color: #E8F0FE;\n",
              "      --fill-color: #1967D2;\n",
              "      --hover-bg-color: #E2EBFA;\n",
              "      --hover-fill-color: #174EA6;\n",
              "      --disabled-fill-color: #AAA;\n",
              "      --disabled-bg-color: #DDD;\n",
              "  }\n",
              "\n",
              "  [theme=dark] .colab-df-quickchart {\n",
              "      --bg-color: #3B4455;\n",
              "      --fill-color: #D2E3FC;\n",
              "      --hover-bg-color: #434B5C;\n",
              "      --hover-fill-color: #FFFFFF;\n",
              "      --disabled-bg-color: #3B4455;\n",
              "      --disabled-fill-color: #666;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart {\n",
              "    background-color: var(--bg-color);\n",
              "    border: none;\n",
              "    border-radius: 50%;\n",
              "    cursor: pointer;\n",
              "    display: none;\n",
              "    fill: var(--fill-color);\n",
              "    height: 32px;\n",
              "    padding: 0;\n",
              "    width: 32px;\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart:hover {\n",
              "    background-color: var(--hover-bg-color);\n",
              "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "    fill: var(--button-hover-fill-color);\n",
              "  }\n",
              "\n",
              "  .colab-df-quickchart-complete:disabled,\n",
              "  .colab-df-quickchart-complete:disabled:hover {\n",
              "    background-color: var(--disabled-bg-color);\n",
              "    fill: var(--disabled-fill-color);\n",
              "    box-shadow: none;\n",
              "  }\n",
              "\n",
              "  .colab-df-spinner {\n",
              "    border: 2px solid var(--fill-color);\n",
              "    border-color: transparent;\n",
              "    border-bottom-color: var(--fill-color);\n",
              "    animation:\n",
              "      spin 1s steps(1) infinite;\n",
              "  }\n",
              "\n",
              "  @keyframes spin {\n",
              "    0% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "      border-left-color: var(--fill-color);\n",
              "    }\n",
              "    20% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    30% {\n",
              "      border-color: transparent;\n",
              "      border-left-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    40% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-top-color: var(--fill-color);\n",
              "    }\n",
              "    60% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "    }\n",
              "    80% {\n",
              "      border-color: transparent;\n",
              "      border-right-color: var(--fill-color);\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "    90% {\n",
              "      border-color: transparent;\n",
              "      border-bottom-color: var(--fill-color);\n",
              "    }\n",
              "  }\n",
              "</style>\n",
              "\n",
              "  <script>\n",
              "    async function quickchart(key) {\n",
              "      const quickchartButtonEl =\n",
              "        document.querySelector('#' + key + ' button');\n",
              "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
              "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
              "      try {\n",
              "        const charts = await google.colab.kernel.invokeFunction(\n",
              "            'suggestCharts', [key], {});\n",
              "      } catch (error) {\n",
              "        console.error('Error during call to suggestCharts:', error);\n",
              "      }\n",
              "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
              "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
              "    }\n",
              "    (() => {\n",
              "      let quickchartButtonEl =\n",
              "        document.querySelector('#df-be397270-aa2d-4c6f-809a-05c75d3d568b button');\n",
              "      quickchartButtonEl.style.display =\n",
              "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "    })();\n",
              "  </script>\n",
              "</div>\n",
              "\n",
              "    </div>\n",
              "  </div>\n"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "dataframe",
              "summary": "{\n  \"name\": \"display(pd\",\n  \"rows\": 3647,\n  \"fields\": [\n    {\n      \"column\": \"country_1\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 225,\n        \"samples\": [\n          \"Suriname\",\n          \"Liechtenstein\",\n          \"Kenya\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"country_2\",\n      \"properties\": {\n        \"dtype\": \"category\",\n        \"num_unique_values\": 223,\n        \"samples\": [\n          \"Trinidad And Tobago\",\n          \"Sierra Leone\",\n          \"Taiwan\"\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"avg_temp_country_1\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 5.672871363711301,\n        \"min\": -15.8,\n        \"max\": 30.03,\n        \"num_unique_values\": 206,\n        \"samples\": [\n          24.16,\n          9.78,\n          9.22\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"avg_temp_country_2\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 5.684454840645034,\n        \"min\": -16.3,\n        \"max\": 30.03,\n        \"num_unique_values\": 206,\n        \"samples\": [\n          24.16,\n          9.79,\n          19.54\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    },\n    {\n      \"column\": \"temperature_difference\",\n      \"properties\": {\n        \"dtype\": \"number\",\n        \"std\": 0.2855989452879363,\n        \"min\": 0.0,\n        \"max\": 1.0,\n        \"num_unique_values\": 101,\n        \"samples\": [\n          0.84,\n          0.55,\n          0.66\n        ],\n        \"semantic_type\": \"\",\n        \"description\": \"\"\n      }\n    }\n  ]\n}"
            }
          },
          "metadata": {}
        }
      ]
    }
  ]
}