8.12.Filtering and analysing summary statistic report [Notebook].ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "bf17eebd",
   "metadata": {},
   "source": [
    "<div align=\"right\" style=\" font-size: 80%; text-align: center; margin: 0 auto\">\n",
    "<img src=\"https://raw.githubusercontent.com/Explore-AI/Pictures/master/ExploreAI_logos/Logo blue_dark.png\"  style=\"width:25px\" align=\"right\";/>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f662d169",
   "metadata": {},
   "source": [
    "# Filtering and analysing a summary statistic report\n",
    "© ExploreAI Academy"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "26af890c",
   "metadata": {},
   "source": [
    "In this notebook, we demonstrate how to filter and analyse a summary statistic report using the `WHERE` and `HAVING` clauses."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3259ef42",
   "metadata": {},
   "source": [
    "> ⚠️ This notebook will not run on Google Colab because it cannot connect to a local database. Please make sure that this notebook is running on the same local machine as your MySQL Workbench installation and MySQL `united_nations` database."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "2d230d14",
   "metadata": {},
   "source": [
    "## Learning objectives\n",
    "\n",
    "By the end of this train, you should:\n",
    "-  Know how to analyse a summary statistic report using the `WHERE` and `HAVING` clauses."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "208f05c1",
   "metadata": {},
   "source": [
    "## Connecting to our MySQL database\n",
    "\n",
    "\n",
    "Continuing with the numerical analysis of our Access_to_Basic_Services table created in MySQL Workbench, we want to filter and analyse our already-created summary statistic report. We can apply the same queries in MySQL Workbench and in this notebook if we connect to our MySQL server. Since we have a MySQL database, we can connect to it using mysql and pymysql."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d6fbcc54",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load and activate the SQL extension to allow us to execute SQL in a Jupyter notebook. \n",
    "# If you get an error here, make sure that mysql and pymysql are installed correctly. \n",
    "\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "038c6354",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish a connection to the local database using the '%sql' magic command.\n",
    "# Replace 'password' with our connection password and `db_name` with our database name. \n",
    "# If you get an error here, please make sure the database name or password is correct.\n",
    "\n",
    "%sql mysql+pymysql://root:184253@localhost:3306/united_nations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73462176",
   "metadata": {},
   "source": [
    "To make a query, we add the `%%sql` command to the start of a cell, create one open line, and then the query like below, and run the cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "6753712c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Sub_region</th>\n",
       "            <th>Country_name</th>\n",
       "            <th>Time_period</th>\n",
       "            <th>Pct_managed_drinking_water_services</th>\n",
       "            <th>Pct_managed_sanitation_services</th>\n",
       "            <th>Est_population_in_millions</th>\n",
       "            <th>Est_gdp_in_billions</th>\n",
       "            <th>Land_area</th>\n",
       "            <th>Pct_unemployment</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>Kazakhstan</td>\n",
       "            <td>2015</td>\n",
       "            <td>94.67</td>\n",
       "            <td>98.00</td>\n",
       "            <td>17.542806</td>\n",
       "            <td>184.39</td>\n",
       "            <td>2699700.00</td>\n",
       "            <td>4.93</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>Kazakhstan</td>\n",
       "            <td>2016</td>\n",
       "            <td>94.67</td>\n",
       "            <td>98.00</td>\n",
       "            <td>17.794055</td>\n",
       "            <td>137.28</td>\n",
       "            <td>2699700.00</td>\n",
       "            <td>4.96</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>Kazakhstan</td>\n",
       "            <td>2017</td>\n",
       "            <td>95.00</td>\n",
       "            <td>98.00</td>\n",
       "            <td>18.037776</td>\n",
       "            <td>166.81</td>\n",
       "            <td>2699700.00</td>\n",
       "            <td>4.90</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>Kazakhstan</td>\n",
       "            <td>2018</td>\n",
       "            <td>95.00</td>\n",
       "            <td>98.00</td>\n",
       "            <td>18.276452</td>\n",
       "            <td>179.34</td>\n",
       "            <td>2699700.00</td>\n",
       "            <td>4.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>Kazakhstan</td>\n",
       "            <td>2019</td>\n",
       "            <td>95.00</td>\n",
       "            <td>98.00</td>\n",
       "            <td>18.513673</td>\n",
       "            <td>181.67</td>\n",
       "            <td>2699700.00</td>\n",
       "            <td>4.80</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Central and Southern Asia', 'Central Asia', 'Kazakhstan', 2015, Decimal('94.67'), Decimal('98.00'), Decimal('17.542806'), Decimal('184.39'), Decimal('2699700.00'), Decimal('4.93')),\n",
       " ('Central and Southern Asia', 'Central Asia', 'Kazakhstan', 2016, Decimal('94.67'), Decimal('98.00'), Decimal('17.794055'), Decimal('137.28'), Decimal('2699700.00'), Decimal('4.96')),\n",
       " ('Central and Southern Asia', 'Central Asia', 'Kazakhstan', 2017, Decimal('95.00'), Decimal('98.00'), Decimal('18.037776'), Decimal('166.81'), Decimal('2699700.00'), Decimal('4.90')),\n",
       " ('Central and Southern Asia', 'Central Asia', 'Kazakhstan', 2018, Decimal('95.00'), Decimal('98.00'), Decimal('18.276452'), Decimal('179.34'), Decimal('2699700.00'), Decimal('4.85')),\n",
       " ('Central and Southern Asia', 'Central Asia', 'Kazakhstan', 2019, Decimal('95.00'), Decimal('98.00'), Decimal('18.513673'), Decimal('181.67'), Decimal('2699700.00'), Decimal('4.80'))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    *\n",
    "FROM\n",
    "    Access_to_Basic_Services\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "859498d1",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "We started with finding out the minimum, maximum, and average percentage of people that have access to drinking water services, the number of countries, and the total GDP per region and sub-region. We also ordered this data by estimated GDP."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "15beb432",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "18 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Sub_region</th>\n",
       "            <th>min_Pct_managed_drinking_water_services</th>\n",
       "            <th>max_Pct_managed_drinking_water_services</th>\n",
       "            <th>avg_Pct_managed_drinking_water_services</th>\n",
       "            <th>Number_of_countries</th>\n",
       "            <th>EST_total_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Polynesia</td>\n",
       "            <td>91.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>98.506481</td>\n",
       "            <td>9</td>\n",
       "            <td>46.76</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Micronesia</td>\n",
       "            <td>73.67</td>\n",
       "            <td>100.00</td>\n",
       "            <td>93.414634</td>\n",
       "            <td>7</td>\n",
       "            <td>46.92</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Melanesia</td>\n",
       "            <td>53.67</td>\n",
       "            <td>99.00</td>\n",
       "            <td>82.700333</td>\n",
       "            <td>5</td>\n",
       "            <td>237.87</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Middle Africa</td>\n",
       "            <td>38.33</td>\n",
       "            <td>77.33</td>\n",
       "            <td>58.967059</td>\n",
       "            <td>9</td>\n",
       "            <td>837.34</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>80.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>93.144667</td>\n",
       "            <td>5</td>\n",
       "            <td>1670.32</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Eastern Africa</td>\n",
       "            <td>46.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>70.128421</td>\n",
       "            <td>20</td>\n",
       "            <td>1944.61</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>Caribbean</td>\n",
       "            <td>64.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>96.005000</td>\n",
       "            <td>27</td>\n",
       "            <td>2070.17</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Southern Africa</td>\n",
       "            <td>73.33</td>\n",
       "            <td>92.00</td>\n",
       "            <td>82.645000</td>\n",
       "            <td>5</td>\n",
       "            <td>2386.64</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northern Africa and Western Asia</td>\n",
       "            <td>Northern Africa</td>\n",
       "            <td>61.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>88.906111</td>\n",
       "            <td>6</td>\n",
       "            <td>2736.80</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Western Africa</td>\n",
       "            <td>53.33</td>\n",
       "            <td>99.00</td>\n",
       "            <td>72.365686</td>\n",
       "            <td>17</td>\n",
       "            <td>3621.31</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>Central America</td>\n",
       "            <td>79.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>93.798125</td>\n",
       "            <td>8</td>\n",
       "            <td>8524.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Australia and New Zealand</td>\n",
       "            <td>100.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>100.000000</td>\n",
       "            <td>2</td>\n",
       "            <td>9241.73</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Europe and Northern America</td>\n",
       "            <td>Northern America</td>\n",
       "            <td>91.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>97.911333</td>\n",
       "            <td>5</td>\n",
       "            <td>9905.96</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northern Africa and Western Asia</td>\n",
       "            <td>Western Asia</td>\n",
       "            <td>59.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>95.031204</td>\n",
       "            <td>18</td>\n",
       "            <td>13605.83</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eastern and South-Eastern Asia</td>\n",
       "            <td>South-Eastern Asia</td>\n",
       "            <td>73.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>90.626061</td>\n",
       "            <td>11</td>\n",
       "            <td>15563.18</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Southern Asia</td>\n",
       "            <td>67.00</td>\n",
       "            <td>99.67</td>\n",
       "            <td>91.894074</td>\n",
       "            <td>9</td>\n",
       "            <td>19824.66</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>South America</td>\n",
       "            <td>86.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>94.880952</td>\n",
       "            <td>14</td>\n",
       "            <td>19959.58</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eastern and South-Eastern Asia</td>\n",
       "            <td>Eastern Asia</td>\n",
       "            <td>75.67</td>\n",
       "            <td>100.00</td>\n",
       "            <td>92.699667</td>\n",
       "            <td>5</td>\n",
       "            <td>107123.37</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Oceania', 'Polynesia', Decimal('91.00'), Decimal('100.00'), Decimal('98.506481'), 9, Decimal('46.76')),\n",
       " ('Oceania', 'Micronesia', Decimal('73.67'), Decimal('100.00'), Decimal('93.414634'), 7, Decimal('46.92')),\n",
       " ('Oceania', 'Melanesia', Decimal('53.67'), Decimal('99.00'), Decimal('82.700333'), 5, Decimal('237.87')),\n",
       " ('Sub-Saharan Africa', 'Middle Africa', Decimal('38.33'), Decimal('77.33'), Decimal('58.967059'), 9, Decimal('837.34')),\n",
       " ('Central and Southern Asia', 'Central Asia', Decimal('80.33'), Decimal('100.00'), Decimal('93.144667'), 5, Decimal('1670.32')),\n",
       " ('Sub-Saharan Africa', 'Eastern Africa', Decimal('46.33'), Decimal('100.00'), Decimal('70.128421'), 20, Decimal('1944.61')),\n",
       " ('Latin America and the Caribbean', 'Caribbean', Decimal('64.00'), Decimal('100.00'), Decimal('96.005000'), 27, Decimal('2070.17')),\n",
       " ('Sub-Saharan Africa', 'Southern Africa', Decimal('73.33'), Decimal('92.00'), Decimal('82.645000'), 5, Decimal('2386.64')),\n",
       " ('Northern Africa and Western Asia', 'Northern Africa', Decimal('61.33'), Decimal('100.00'), Decimal('88.906111'), 6, Decimal('2736.80')),\n",
       " ('Sub-Saharan Africa', 'Western Africa', Decimal('53.33'), Decimal('99.00'), Decimal('72.365686'), 17, Decimal('3621.31')),\n",
       " ('Latin America and the Caribbean', 'Central America', Decimal('79.00'), Decimal('100.00'), Decimal('93.798125'), 8, Decimal('8524.66')),\n",
       " ('Oceania', 'Australia and New Zealand', Decimal('100.00'), Decimal('100.00'), Decimal('100.000000'), 2, Decimal('9241.73')),\n",
       " ('Europe and Northern America', 'Northern America', Decimal('91.00'), Decimal('100.00'), Decimal('97.911333'), 5, Decimal('9905.96')),\n",
       " ('Northern Africa and Western Asia', 'Western Asia', Decimal('59.00'), Decimal('100.00'), Decimal('95.031204'), 18, Decimal('13605.83')),\n",
       " ('Eastern and South-Eastern Asia', 'South-Eastern Asia', Decimal('73.33'), Decimal('100.00'), Decimal('90.626061'), 11, Decimal('15563.18')),\n",
       " ('Central and Southern Asia', 'Southern Asia', Decimal('67.00'), Decimal('99.67'), Decimal('91.894074'), 9, Decimal('19824.66')),\n",
       " ('Latin America and the Caribbean', 'South America', Decimal('86.00'), Decimal('100.00'), Decimal('94.880952'), 14, Decimal('19959.58')),\n",
       " ('Eastern and South-Eastern Asia', 'Eastern Asia', Decimal('75.67'), Decimal('100.00'), Decimal('92.699667'), 5, Decimal('107123.37'))]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services,\n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions \n",
    "FROM united_nations.Access_to_Basic_Services \n",
    "GROUP BY Region, Sub_region\n",
    "ORDER BY EST_total_gdp_in_billions;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70147f13",
   "metadata": {},
   "source": [
    "Let's continue with our analysis.\n",
    "\n",
    "We want to use the summary statistic report to do the following:\n",
    "\n",
    "1. Filter for the year 2020.\n",
    "2. Focus on countries where the percentage of managed drinking water services is below 60%.\n",
    "3. Filter for the regions and sub-regions that have fewer than four countries."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "86802726",
   "metadata": {},
   "source": [
    "### 1. Filter for the year 2020.\n",
    "Using the above query, focus on results where the time period is 2020 using the `WHERE` clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "91f3421f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "18 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Sub_region</th>\n",
       "            <th>min_Pct_managed_drinking_water_services</th>\n",
       "            <th>max_Pct_managed_drinking_water_services</th>\n",
       "            <th>avg_Pct_managed_drinking_water_services</th>\n",
       "            <th>Number_of_countries</th>\n",
       "            <th>EST_total_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Micronesia</td>\n",
       "            <td>77.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>94.500000</td>\n",
       "            <td>6</td>\n",
       "            <td>6.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Polynesia</td>\n",
       "            <td>92.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>98.555556</td>\n",
       "            <td>9</td>\n",
       "            <td>7.84</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Melanesia</td>\n",
       "            <td>56.67</td>\n",
       "            <td>99.00</td>\n",
       "            <td>82.934000</td>\n",
       "            <td>5</td>\n",
       "            <td>40.21</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Middle Africa</td>\n",
       "            <td>38.33</td>\n",
       "            <td>77.33</td>\n",
       "            <td>59.332500</td>\n",
       "            <td>8</td>\n",
       "            <td>123.22</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Central Asia</td>\n",
       "            <td>85.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>94.134000</td>\n",
       "            <td>5</td>\n",
       "            <td>239.10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>Caribbean</td>\n",
       "            <td>65.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>95.910667</td>\n",
       "            <td>15</td>\n",
       "            <td>343.26</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Eastern Africa</td>\n",
       "            <td>48.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>70.018824</td>\n",
       "            <td>17</td>\n",
       "            <td>359.10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Southern Africa</td>\n",
       "            <td>76.33</td>\n",
       "            <td>92.00</td>\n",
       "            <td>83.668000</td>\n",
       "            <td>5</td>\n",
       "            <td>369.34</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northern Africa and Western Asia</td>\n",
       "            <td>Northern Africa</td>\n",
       "            <td>62.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>90.053333</td>\n",
       "            <td>6</td>\n",
       "            <td>386.29</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Western Africa</td>\n",
       "            <td>53.33</td>\n",
       "            <td>99.00</td>\n",
       "            <td>73.607059</td>\n",
       "            <td>17</td>\n",
       "            <td>631.91</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>Central America</td>\n",
       "            <td>79.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>94.498750</td>\n",
       "            <td>8</td>\n",
       "            <td>1347.35</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Australia and New Zealand</td>\n",
       "            <td>100.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>100.000000</td>\n",
       "            <td>2</td>\n",
       "            <td>1538.63</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Europe and Northern America</td>\n",
       "            <td>Northern America</td>\n",
       "            <td>91.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>98.000000</td>\n",
       "            <td>5</td>\n",
       "            <td>1655.39</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Northern Africa and Western Asia</td>\n",
       "            <td>Western Asia</td>\n",
       "            <td>63.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>95.593333</td>\n",
       "            <td>18</td>\n",
       "            <td>2194.63</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eastern and South-Eastern Asia</td>\n",
       "            <td>South-Eastern Asia</td>\n",
       "            <td>75.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>92.120909</td>\n",
       "            <td>11</td>\n",
       "            <td>2721.73</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Latin America and the Caribbean</td>\n",
       "            <td>South America</td>\n",
       "            <td>90.33</td>\n",
       "            <td>100.00</td>\n",
       "            <td>95.667143</td>\n",
       "            <td>14</td>\n",
       "            <td>2755.50</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Central and Southern Asia</td>\n",
       "            <td>Southern Asia</td>\n",
       "            <td>80.33</td>\n",
       "            <td>99.67</td>\n",
       "            <td>93.074444</td>\n",
       "            <td>9</td>\n",
       "            <td>3487.02</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Eastern and South-Eastern Asia</td>\n",
       "            <td>Eastern Asia</td>\n",
       "            <td>81.00</td>\n",
       "            <td>100.00</td>\n",
       "            <td>93.400000</td>\n",
       "            <td>5</td>\n",
       "            <td>19741.09</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Oceania', 'Micronesia', Decimal('77.00'), Decimal('100.00'), Decimal('94.500000'), 6, Decimal('6.67')),\n",
       " ('Oceania', 'Polynesia', Decimal('92.00'), Decimal('100.00'), Decimal('98.555556'), 9, Decimal('7.84')),\n",
       " ('Oceania', 'Melanesia', Decimal('56.67'), Decimal('99.00'), Decimal('82.934000'), 5, Decimal('40.21')),\n",
       " ('Sub-Saharan Africa', 'Middle Africa', Decimal('38.33'), Decimal('77.33'), Decimal('59.332500'), 8, Decimal('123.22')),\n",
       " ('Central and Southern Asia', 'Central Asia', Decimal('85.00'), Decimal('100.00'), Decimal('94.134000'), 5, Decimal('239.10')),\n",
       " ('Latin America and the Caribbean', 'Caribbean', Decimal('65.00'), Decimal('100.00'), Decimal('95.910667'), 15, Decimal('343.26')),\n",
       " ('Sub-Saharan Africa', 'Eastern Africa', Decimal('48.33'), Decimal('100.00'), Decimal('70.018824'), 17, Decimal('359.10')),\n",
       " ('Sub-Saharan Africa', 'Southern Africa', Decimal('76.33'), Decimal('92.00'), Decimal('83.668000'), 5, Decimal('369.34')),\n",
       " ('Northern Africa and Western Asia', 'Northern Africa', Decimal('62.33'), Decimal('100.00'), Decimal('90.053333'), 6, Decimal('386.29')),\n",
       " ('Sub-Saharan Africa', 'Western Africa', Decimal('53.33'), Decimal('99.00'), Decimal('73.607059'), 17, Decimal('631.91')),\n",
       " ('Latin America and the Caribbean', 'Central America', Decimal('79.33'), Decimal('100.00'), Decimal('94.498750'), 8, Decimal('1347.35')),\n",
       " ('Oceania', 'Australia and New Zealand', Decimal('100.00'), Decimal('100.00'), Decimal('100.000000'), 2, Decimal('1538.63')),\n",
       " ('Europe and Northern America', 'Northern America', Decimal('91.00'), Decimal('100.00'), Decimal('98.000000'), 5, Decimal('1655.39')),\n",
       " ('Northern Africa and Western Asia', 'Western Asia', Decimal('63.00'), Decimal('100.00'), Decimal('95.593333'), 18, Decimal('2194.63')),\n",
       " ('Eastern and South-Eastern Asia', 'South-Eastern Asia', Decimal('75.33'), Decimal('100.00'), Decimal('92.120909'), 11, Decimal('2721.73')),\n",
       " ('Latin America and the Caribbean', 'South America', Decimal('90.33'), Decimal('100.00'), Decimal('95.667143'), 14, Decimal('2755.50')),\n",
       " ('Central and Southern Asia', 'Southern Asia', Decimal('80.33'), Decimal('99.67'), Decimal('93.074444'), 9, Decimal('3487.02')),\n",
       " ('Eastern and South-Eastern Asia', 'Eastern Asia', Decimal('81.00'), Decimal('100.00'), Decimal('93.400000'), 5, Decimal('19741.09'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services,\n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions \n",
    "FROM united_nations.Access_to_Basic_Services\n",
    "WHERE Time_period = 2020\n",
    "GROUP BY Region, Sub_region\n",
    "ORDER BY EST_total_gdp_in_billions;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f0fade51",
   "metadata": {},
   "source": [
    "### 2. Focus on countries where the percentage of managed drinking water services is below 60%.\n",
    "\n",
    "Adding onto your query above, focus on results where the percentage of managed drinking water services is smaller than 60% using the `WHERE` clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "748f4263",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Sub_region</th>\n",
       "            <th>min_Pct_managed_drinking_water_services</th>\n",
       "            <th>max_Pct_managed_drinking_water_services</th>\n",
       "            <th>avg_Pct_managed_drinking_water_services</th>\n",
       "            <th>Number_of_countries</th>\n",
       "            <th>EST_total_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Melanesia</td>\n",
       "            <td>56.67</td>\n",
       "            <td>56.67</td>\n",
       "            <td>56.670000</td>\n",
       "            <td>1</td>\n",
       "            <td>23.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Western Africa</td>\n",
       "            <td>53.33</td>\n",
       "            <td>57.33</td>\n",
       "            <td>55.330000</td>\n",
       "            <td>2</td>\n",
       "            <td>31.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Middle Africa</td>\n",
       "            <td>38.33</td>\n",
       "            <td>52.67</td>\n",
       "            <td>47.750000</td>\n",
       "            <td>4</td>\n",
       "            <td>66.67</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Eastern Africa</td>\n",
       "            <td>48.33</td>\n",
       "            <td>58.00</td>\n",
       "            <td>54.997500</td>\n",
       "            <td>4</td>\n",
       "            <td>127.59</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Oceania', 'Melanesia', Decimal('56.67'), Decimal('56.67'), Decimal('56.670000'), 1, Decimal('23.85')),\n",
       " ('Sub-Saharan Africa', 'Western Africa', Decimal('53.33'), Decimal('57.33'), Decimal('55.330000'), 2, Decimal('31.67')),\n",
       " ('Sub-Saharan Africa', 'Middle Africa', Decimal('38.33'), Decimal('52.67'), Decimal('47.750000'), 4, Decimal('66.67')),\n",
       " ('Sub-Saharan Africa', 'Eastern Africa', Decimal('48.33'), Decimal('58.00'), Decimal('54.997500'), 4, Decimal('127.59'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services,\n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions \n",
    "FROM united_nations.Access_to_Basic_Services\n",
    "WHERE Time_period = 2020\n",
    "  AND Pct_managed_drinking_water_services < 60\n",
    "GROUP BY Region, Sub_region\n",
    "ORDER BY EST_total_gdp_in_billions;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f28aef00",
   "metadata": {},
   "source": [
    "### 3. Filter for the sub-regions that have fewer than four countries.\n",
    "\n",
    "Filter the results above to only include the regions and sub-regions that have fewer than four countries in the Number_of_countries alias using the `HAVING` clause."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f4621a53",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * mysql+pymysql://root:***@localhost:3306/united_nations\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>Region</th>\n",
       "            <th>Sub_region</th>\n",
       "            <th>min_Pct_managed_drinking_water_services</th>\n",
       "            <th>max_Pct_managed_drinking_water_services</th>\n",
       "            <th>avg_Pct_managed_drinking_water_services</th>\n",
       "            <th>Number_of_countries</th>\n",
       "            <th>EST_total_gdp_in_billions</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Oceania</td>\n",
       "            <td>Melanesia</td>\n",
       "            <td>56.67</td>\n",
       "            <td>56.67</td>\n",
       "            <td>56.670000</td>\n",
       "            <td>1</td>\n",
       "            <td>23.85</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Sub-Saharan Africa</td>\n",
       "            <td>Western Africa</td>\n",
       "            <td>53.33</td>\n",
       "            <td>57.33</td>\n",
       "            <td>55.330000</td>\n",
       "            <td>2</td>\n",
       "            <td>31.67</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Oceania', 'Melanesia', Decimal('56.67'), Decimal('56.67'), Decimal('56.670000'), 1, Decimal('23.85')),\n",
       " ('Sub-Saharan Africa', 'Western Africa', Decimal('53.33'), Decimal('57.33'), Decimal('55.330000'), 2, Decimal('31.67'))]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services,\n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions \n",
    "FROM united_nations.Access_to_Basic_Services\n",
    "WHERE Time_period = 2020\n",
    "  AND Pct_managed_drinking_water_services < 60\n",
    "GROUP BY Region, Sub_region\n",
    "HAVING Number_of_countries < 4\n",
    "ORDER BY EST_total_gdp_in_billions;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3c88335",
   "metadata": {},
   "source": [
    "## Solutions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5084b12",
   "metadata": {},
   "source": [
    "### 1. Filter for the year 2020."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c968d875",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services, \n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions\n",
    "FROM Access_to_Basic_Services \n",
    "WHERE Time_period = 2020\n",
    "GROUP BY Region, Sub_region\n",
    "ORDER BY EST_total_gdp_in_billions ASC;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "294d8eef",
   "metadata": {},
   "source": [
    "### 2. Focus on countries where the percentage of managed drinking water services is below 60%."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03071e34",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services, \n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions\n",
    "FROM Access_to_Basic_Services \n",
    "WHERE Time_period = 2020\n",
    "    AND Pct_managed_drinking_water_services < 60\n",
    "GROUP BY Region, Sub_region\n",
    "ORDER BY EST_total_gdp_in_billions ASC;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eafe40b6",
   "metadata": {},
   "source": [
    "### 3. Filter for the regions and sub-regions that have fewer than four countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ffd2239",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT Region, \n",
    "    Sub_region,\n",
    "    MIN(Pct_managed_drinking_water_services) AS min_Pct_managed_drinking_water_services, \n",
    "    MAX(Pct_managed_drinking_water_services) AS max_Pct_managed_drinking_water_services, \n",
    "    AVG(Pct_managed_drinking_water_services) AS avg_Pct_managed_drinking_water_services, \n",
    "    COUNT(DISTINCT(Country_name)) AS Number_of_countries,\n",
    "    SUM(EST_gdp_in_billions) AS EST_total_gdp_in_billions\n",
    "FROM Access_to_Basic_Services \n",
    "WHERE Time_period = 2020\n",
    "    AND Pct_managed_drinking_water_services < 60\n",
    "GROUP BY Region, Sub_region\n",
    "HAVING Number_of_countries < 4\n",
    "ORDER BY EST_total_gdp_in_billions ASC;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "471bb8f9",
   "metadata": {},
   "source": [
    "The `WHERE` clause may come to mind first when trying to apply this criterion. However, take note that this criterion may only be used after the \"Number_of_countries\" aggregate and grouping. This is because we want to group the data by region and sub-region and then only choose the groups that have fewer than four countries inside those groups.\n",
    "Because the `WHERE` clause executes before the aggregate and GROUP BY clauses, we are unable to use it.\n",
    "Therefore the `HAVING` clause is more appropriate here."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "018cd50f",
   "metadata": {},
   "source": [
    "With this report, we can answer questions like “Out of the sub-regions that meet the criteria, which sub-region has the lowest GDP?”\n",
    "Since the data is ordered according to the estimated GDP in ascending order, the first result in the sub-region column will contain the answer. That is, Melanesia at a GDP of 23.85 billion.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d8d9bdd",
   "metadata": {},
   "source": [
    "<div align=\"center\" style=\" font-size: 80%; text-align: center; margin: 0 auto\">\n",
    "<img src=\"https://raw.githubusercontent.com/Explore-AI/Pictures/master/ExploreAI_logos/EAI_Blue_Dark.png\"  style=\"width:200px\";/>\n",
    "</div>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}