19. Practical Statistics _ Descriptive with SQL.ipynb

{"cells":[{"cell_type":"markdown","metadata":{"id":"sJ6Bgje2a1su"},"source":["# Day 2 PM - Practical Descriptive Statistics on SQL\n","\n","You have already learnt basic SQL query syntaxes including DML, DDL, and DQL, especially today, we learn about group by, rank, and row_number. In this notebook, we will use our SQL knowledge to solve descriptive statistics problems. To narrowing our learning scope, we only use sqlite but in general, the concept and the syntax are not different so far. Furthermore, you can use other IDE such as Dbeaver, Datagrip, Navicat, etc."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"wZCKRvD7a1sz"},"outputs":[],"source":["import sqlite3\n","import pandas as pd"]},{"cell_type":"markdown","metadata":{"id":"71DoyaFLa1s1"},"source":["## The Dataset\n","For this learning, we use data from Our World in Data about air pollution. You can access the github to look at this data or others: https://github.com/owid/owid-datasets/tree/master/datasets . You don't need download any data, we access the link directly using Pandas.\n","\n","We have two csv files which are air pollutant emission from OECD and air pollutant death breakdown by age from IMHE. The description of each data delivered below:\n","\n","1. **Air pollutant emission by OECD**\\\n","Air pollutant emissions reported for OECD countries, where data is available, measured in tonnes per year.\\\n","Indexed figures relate to changes since the year 1990 (1990 is assumed equal to 100). A figure lower than 100 indicates a reduction in emissions (e.g. 40 indicates a 60% reduction since 1990). Indexed figures are only available for countries with data extending to 1990.\n","\n","\n","2. **Air pollutant death breakdown by IMHE**\\\n","Data relates to the number of deaths attributed to air pollution across age groups. This is measured based on attribution to all linked causes.\\\n","IHME have developed a Comparative Risk Assessment (CRA) conceptual framework by which they have built a web of risk factors or causes which affect health outcomes.\\\n","Such risk-outcome pairs (e.g. air pollution and lung cancer) are formed based on evidence links using methods such as cohort studies, randomised trials, and case-control studies. Once a risk-outcome pair has been formed, how does IHME begin to quantify the disease burden or number of deaths attributed to each risk? The CRA can be used for two different types of assessment: attributable burden and avoidable burden. 'Attributable burden' represents the reduction in current disease burden (or that of a given year) if population exposure had shifted to another counterfactual/hypothetical exposure level; 'avoidable burden' represents the potential future avoided burden if population exposure was to shift to a counterfactual level of exposure. Since the number of deaths is based on current or historical data, the data presented here is that of attributable burden."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"CRy8gO6wa1s2"},"outputs":[],"source":["air_pollutant_emission = pd.read_csv('https://github.com/owid/owid-datasets/raw/master/datasets/Air%20Pollutant%20Emissions%20-%20OECD/Air%20Pollutant%20Emissions%20-%20OECD.csv')\n","air_pol_death_by_age = pd.read_csv('https://raw.githubusercontent.com/owid/owid-datasets/7c8e4abdfde611322781da5fbc9d1b8333645370/datasets/Air%20pollution%20deaths%20breakdown%20by%20age%20-%20IHME/Air%20pollution%20deaths%20breakdown%20by%20age%20-%20IHME.csv')"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"y8ylI9Gxa1s3","outputId":"76b5d483-9a12-4912-bffc-9e3f09b701ab"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>Carbon Monoxide</th>\n","      <th>NOx</th>\n","      <th>Non-methane Volatile Organic Compounds (VOCs)</th>\n","      <th>PM₁₀</th>\n","      <th>PM₂.₅</th>\n","      <th>SO₂</th>\n","      <th>Carbon Monoxide (Index)</th>\n","      <th>NOx (Index)</th>\n","      <th>Non-methane Volatile Organic Compounds (VOCs) (Index)</th>\n","      <th>PM₁₀ (Index)</th>\n","      <th>PM₂.₅ (Index)</th>\n","      <th>SO₂ (Index)</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Australia</td>\n","      <td>1990</td>\n","      <td>5729</td>\n","      <td>1621</td>\n","      <td>1387.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>1555</td>\n","      <td>100.00</td>\n","      <td>100.00</td>\n","      <td>100.00</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>100.00</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Australia</td>\n","      <td>1991</td>\n","      <td>5747</td>\n","      <td>1596</td>\n","      <td>1373.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>1598</td>\n","      <td>100.33</td>\n","      <td>98.48</td>\n","      <td>98.96</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>102.74</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Australia</td>\n","      <td>1992</td>\n","      <td>5897</td>\n","      <td>1642</td>\n","      <td>1374.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>1707</td>\n","      <td>102.93</td>\n","      <td>101.31</td>\n","      <td>99.08</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>109.76</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Australia</td>\n","      <td>1993</td>\n","      <td>6122</td>\n","      <td>1685</td>\n","      <td>1390.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>1757</td>\n","      <td>106.87</td>\n","      <td>103.97</td>\n","      <td>100.24</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>112.97</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Australia</td>\n","      <td>1994</td>\n","      <td>6263</td>\n","      <td>1687</td>\n","      <td>1403.0</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>1819</td>\n","      <td>109.33</td>\n","      <td>104.08</td>\n","      <td>101.12</td>\n","      <td>NaN</td>\n","      <td>NaN</td>\n","      <td>116.97</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["      Entity  Year  Carbon Monoxide   NOx  \\\n","0  Australia  1990             5729  1621   \n","1  Australia  1991             5747  1596   \n","2  Australia  1992             5897  1642   \n","3  Australia  1993             6122  1685   \n","4  Australia  1994             6263  1687   \n","\n","   Non-methane Volatile Organic Compounds (VOCs)  PM₁₀  PM₂.₅   SO₂  \\\n","0                                         1387.0   NaN    NaN  1555   \n","1                                         1373.0   NaN    NaN  1598   \n","2                                         1374.0   NaN    NaN  1707   \n","3                                         1390.0   NaN    NaN  1757   \n","4                                         1403.0   NaN    NaN  1819   \n","\n","   Carbon Monoxide (Index)  NOx (Index)  \\\n","0                   100.00       100.00   \n","1                   100.33        98.48   \n","2                   102.93       101.31   \n","3                   106.87       103.97   \n","4                   109.33       104.08   \n","\n","   Non-methane Volatile Organic Compounds (VOCs) (Index)  PM₁₀ (Index)  \\\n","0                                             100.00               NaN   \n","1                                              98.96               NaN   \n","2                                              99.08               NaN   \n","3                                             100.24               NaN   \n","4                                             101.12               NaN   \n","\n","   PM₂.₅ (Index)  SO₂ (Index)  \n","0            NaN       100.00  \n","1            NaN       102.74  \n","2            NaN       109.76  \n","3            NaN       112.97  \n","4            NaN       116.97  "]},"execution_count":4,"metadata":{},"output_type":"execute_result"}],"source":["air_pollutant_emission.head()"]},{"cell_type":"markdown","metadata":{"id":"gpLR7KrOa1s4"},"source":["General overview of air pollutant emission data. We see that there are some nulls in our data. let check them out that how much the nulls are in each column."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"BieCL0moa1s5","outputId":"ab8924f1-9eb2-4cbf-c91f-5678d85a4b08"},"outputs":[{"name":"stdout","output_type":"stream","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 910 entries, 0 to 909\n","Data columns (total 14 columns):\n"," #   Column                                                 Non-Null Count  Dtype  \n","---  ------                                                 --------------  -----  \n"," 0   Entity                                                 910 non-null    object \n"," 1   Year                                                   910 non-null    int64  \n"," 2   Carbon Monoxide                                        910 non-null    int64  \n"," 3   NOx                                                    910 non-null    int64  \n"," 4   Non-methane Volatile Organic Compounds (VOCs)          908 non-null    float64\n"," 5   PM₁₀                                                   636 non-null    float64\n"," 6   PM₂.₅                                                  616 non-null    float64\n"," 7   SO₂                                                    910 non-null    int64  \n"," 8   Carbon Monoxide (Index)                                858 non-null    float64\n"," 9   NOx (Index)                                            858 non-null    float64\n"," 10  Non-methane Volatile Organic Compounds (VOCs) (Index)  858 non-null    float64\n"," 11  PM₁₀ (Index)                                           460 non-null    float64\n"," 12  PM₂.₅ (Index)                                          434 non-null    float64\n"," 13  SO₂ (Index)                                            858 non-null    float64\n","dtypes: float64(9), int64(4), object(1)\n","memory usage: 99.7+ KB\n"]}],"source":["air_pollutant_emission.info()"]},{"cell_type":"markdown","metadata":{"id":"5ry2UPfla1s6"},"source":["The actual size of the data is 910 rows and seem that several columns severe the nulls. Since the data is numerical, we assume that the nulls can be imputed by 0, which mean no data. The aim of missing values handling in this hands on, we want to ease our work when we input this data into SQL table. We do not want to make our life difficult, don't we?"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"uT7lt1sea1s6","outputId":"668b2ffa-0305-451d-84fa-b4fe045927ba"},"outputs":[{"name":"stdout","output_type":"stream","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 910 entries, 0 to 909\n","Data columns (total 14 columns):\n"," #   Column                                                 Non-Null Count  Dtype  \n","---  ------                                                 --------------  -----  \n"," 0   Entity                                                 910 non-null    object \n"," 1   Year                                                   910 non-null    int64  \n"," 2   Carbon Monoxide                                        910 non-null    int64  \n"," 3   NOx                                                    910 non-null    int64  \n"," 4   Non-methane Volatile Organic Compounds (VOCs)          910 non-null    float64\n"," 5   PM₁₀                                                   910 non-null    float64\n"," 6   PM₂.₅                                                  910 non-null    float64\n"," 7   SO₂                                                    910 non-null    int64  \n"," 8   Carbon Monoxide (Index)                                910 non-null    float64\n"," 9   NOx (Index)                                            910 non-null    float64\n"," 10  Non-methane Volatile Organic Compounds (VOCs) (Index)  910 non-null    float64\n"," 11  PM₁₀ (Index)                                           910 non-null    float64\n"," 12  PM₂.₅ (Index)                                          910 non-null    float64\n"," 13  SO₂ (Index)                                            910 non-null    float64\n","dtypes: float64(9), int64(4), object(1)\n","memory usage: 99.7+ KB\n"]}],"source":["air_pollutant_emission.fillna(0,inplace=True)\n","air_pollutant_emission.info()"]},{"cell_type":"markdown","metadata":{"id":"U-zgykixa1s8"},"source":["Hurray! Our data is cleaned from missing value, at least there are no nulls anymore."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"fGAeheRla1s8","outputId":"1489fb47-9f00-4926-efe6-21ca6821da07"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>Indoor (10 to 14 years)</th>\n","      <th>Indoor (15 to 19 years)</th>\n","      <th>Indoor (20 to 24 years)</th>\n","      <th>Indoor (25 to 29 years)</th>\n","      <th>Indoor (30 to 34 years)</th>\n","      <th>Indoor (35 to 39 years)</th>\n","      <th>Indoor (40 to 44 years)</th>\n","      <th>Indoor (45 to 49 years)</th>\n","      <th>...</th>\n","      <th>Outdoor (45 to 49 years)</th>\n","      <th>Outdoor (5 to 9 years)</th>\n","      <th>Outdoor (50 to 54 years)</th>\n","      <th>Outdoor (55 to 59 years)</th>\n","      <th>Outdoor (60 to 64 years)</th>\n","      <th>Outdoor (65 to 69 years)</th>\n","      <th>Outdoor (70 to 74 years)</th>\n","      <th>Outdoor (75 to 79 years)</th>\n","      <th>Outdoor (80+ years)</th>\n","      <th>Outdoor (Under-5s)</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Afghanistan</td>\n","      <td>1990</td>\n","      <td>78</td>\n","      <td>45</td>\n","      <td>43</td>\n","      <td>235</td>\n","      <td>335</td>\n","      <td>523</td>\n","      <td>830</td>\n","      <td>1006</td>\n","      <td>...</td>\n","      <td>676</td>\n","      <td>79</td>\n","      <td>790</td>\n","      <td>823</td>\n","      <td>954</td>\n","      <td>1045</td>\n","      <td>890</td>\n","      <td>576</td>\n","      <td>440</td>\n","      <td>5416</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Afghanistan</td>\n","      <td>1995</td>\n","      <td>111</td>\n","      <td>65</td>\n","      <td>62</td>\n","      <td>347</td>\n","      <td>480</td>\n","      <td>739</td>\n","      <td>1141</td>\n","      <td>1620</td>\n","      <td>...</td>\n","      <td>1068</td>\n","      <td>108</td>\n","      <td>1269</td>\n","      <td>1331</td>\n","      <td>1385</td>\n","      <td>1477</td>\n","      <td>1376</td>\n","      <td>947</td>\n","      <td>644</td>\n","      <td>6067</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Afghanistan</td>\n","      <td>2000</td>\n","      <td>125</td>\n","      <td>76</td>\n","      <td>73</td>\n","      <td>421</td>\n","      <td>592</td>\n","      <td>895</td>\n","      <td>1366</td>\n","      <td>1858</td>\n","      <td>...</td>\n","      <td>1237</td>\n","      <td>116</td>\n","      <td>1637</td>\n","      <td>1670</td>\n","      <td>1695</td>\n","      <td>1661</td>\n","      <td>1583</td>\n","      <td>1201</td>\n","      <td>898</td>\n","      <td>6062</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Afghanistan</td>\n","      <td>2005</td>\n","      <td>184</td>\n","      <td>93</td>\n","      <td>90</td>\n","      <td>511</td>\n","      <td>702</td>\n","      <td>1034</td>\n","      <td>1520</td>\n","      <td>2065</td>\n","      <td>...</td>\n","      <td>1332</td>\n","      <td>206</td>\n","      <td>1714</td>\n","      <td>1960</td>\n","      <td>1923</td>\n","      <td>1873</td>\n","      <td>1626</td>\n","      <td>1236</td>\n","      <td>1035</td>\n","      <td>6771</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Afghanistan</td>\n","      <td>2006</td>\n","      <td>183</td>\n","      <td>92</td>\n","      <td>89</td>\n","      <td>517</td>\n","      <td>714</td>\n","      <td>1051</td>\n","      <td>1539</td>\n","      <td>2082</td>\n","      <td>...</td>\n","      <td>1364</td>\n","      <td>204</td>\n","      <td>1746</td>\n","      <td>2007</td>\n","      <td>2006</td>\n","      <td>1931</td>\n","      <td>1686</td>\n","      <td>1274</td>\n","      <td>1082</td>\n","      <td>6511</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>5 rows × 36 columns</p>\n","</div>"],"text/plain":["        Entity  Year  Indoor (10 to 14 years)  Indoor (15 to 19 years)  \\\n","0  Afghanistan  1990                       78                       45   \n","1  Afghanistan  1995                      111                       65   \n","2  Afghanistan  2000                      125                       76   \n","3  Afghanistan  2005                      184                       93   \n","4  Afghanistan  2006                      183                       92   \n","\n","   Indoor (20 to 24 years)  Indoor (25 to 29 years)  Indoor (30 to 34 years)  \\\n","0                       43                      235                      335   \n","1                       62                      347                      480   \n","2                       73                      421                      592   \n","3                       90                      511                      702   \n","4                       89                      517                      714   \n","\n","   Indoor (35 to 39 years)  Indoor (40 to 44 years)  Indoor (45 to 49 years)  \\\n","0                      523                      830                     1006   \n","1                      739                     1141                     1620   \n","2                      895                     1366                     1858   \n","3                     1034                     1520                     2065   \n","4                     1051                     1539                     2082   \n","\n","   ...  Outdoor (45 to 49 years)  Outdoor (5 to 9 years)  \\\n","0  ...                       676                      79   \n","1  ...                      1068                     108   \n","2  ...                      1237                     116   \n","3  ...                      1332                     206   \n","4  ...                      1364                     204   \n","\n","   Outdoor (50 to 54 years)  Outdoor (55 to 59 years)  \\\n","0                       790                       823   \n","1                      1269                      1331   \n","2                      1637                      1670   \n","3                      1714                      1960   \n","4                      1746                      2007   \n","\n","   Outdoor (60 to 64 years)  Outdoor (65 to 69 years)  \\\n","0                       954                      1045   \n","1                      1385                      1477   \n","2                      1695                      1661   \n","3                      1923                      1873   \n","4                      2006                      1931   \n","\n","   Outdoor (70 to 74 years)  Outdoor (75 to 79 years)   Outdoor (80+ years)  \\\n","0                       890                       576                   440   \n","1                      1376                       947                   644   \n","2                      1583                      1201                   898   \n","3                      1626                      1236                  1035   \n","4                      1686                      1274                  1082   \n","\n","   Outdoor (Under-5s)  \n","0                5416  \n","1                6067  \n","2                6062  \n","3                6771  \n","4                6511  \n","\n","[5 rows x 36 columns]"]},"execution_count":7,"metadata":{},"output_type":"execute_result"}],"source":["air_pol_death_by_age.head()"]},{"cell_type":"markdown","metadata":{"id":"eQzBUclua1s9"},"source":["We can see above that our second data seems okay, we have 36 columns which is a lot of work to do later. However, we have to check the data summary whether the nulls exist or not."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"f8ScAs3Ja1s9","outputId":"ecf5463d-ef8c-43a2-dfca-cc4f888cfebc"},"outputs":[{"name":"stdout","output_type":"stream","text":["<class 'pandas.core.frame.DataFrame'>\n","RangeIndex: 1547 entries, 0 to 1546\n","Data columns (total 36 columns):\n"," #   Column                    Non-Null Count  Dtype \n","---  ------                    --------------  ----- \n"," 0   Entity                    1547 non-null   object\n"," 1   Year                      1547 non-null   int64 \n"," 2   Indoor (10 to 14 years)   1547 non-null   int64 \n"," 3   Indoor (15 to 19 years)   1547 non-null   int64 \n"," 4   Indoor (20 to 24 years)   1547 non-null   int64 \n"," 5   Indoor (25 to 29 years)   1547 non-null   int64 \n"," 6   Indoor (30 to 34 years)   1547 non-null   int64 \n"," 7   Indoor (35 to 39 years)   1547 non-null   int64 \n"," 8   Indoor (40 to 44 years)   1547 non-null   int64 \n"," 9   Indoor (45 to 49 years)   1547 non-null   int64 \n"," 10  Indoor (5 to 9 years)     1547 non-null   int64 \n"," 11  Indoor (50 to 54 years)   1547 non-null   int64 \n"," 12  Indoor (55 to 59 years)   1547 non-null   int64 \n"," 13  Indoor (60 to 64 years)   1547 non-null   int64 \n"," 14  Indoor (65 to 69 years)   1547 non-null   int64 \n"," 15  Indoor (70 to 74 years)   1547 non-null   int64 \n"," 16  Indoor (75 to 79 years)   1547 non-null   int64 \n"," 17  Indoor (80+ years)        1547 non-null   int64 \n"," 18  Indoor (Under-5s)         1547 non-null   int64 \n"," 19  Outdoor (10 to 14 years)  1547 non-null   int64 \n"," 20  Outdoor (15 to 19 years)  1547 non-null   int64 \n"," 21  Outdoor (20 to 24 years)  1547 non-null   int64 \n"," 22  Outdoor (25 to 29 years)  1547 non-null   int64 \n"," 23  Outdoor (30 to 34 years)  1547 non-null   int64 \n"," 24  Outdoor (35 to 39 years)  1547 non-null   int64 \n"," 25  Outdoor (40 to 44 years)  1547 non-null   int64 \n"," 26  Outdoor (45 to 49 years)  1547 non-null   int64 \n"," 27  Outdoor (5 to 9 years)    1547 non-null   int64 \n"," 28  Outdoor (50 to 54 years)  1547 non-null   int64 \n"," 29  Outdoor (55 to 59 years)  1547 non-null   int64 \n"," 30  Outdoor (60 to 64 years)  1547 non-null   int64 \n"," 31  Outdoor (65 to 69 years)  1547 non-null   int64 \n"," 32  Outdoor (70 to 74 years)  1547 non-null   int64 \n"," 33  Outdoor (75 to 79 years)  1547 non-null   int64 \n"," 34   Outdoor (80+ years)      1547 non-null   int64 \n"," 35  Outdoor (Under-5s)        1547 non-null   int64 \n","dtypes: int64(35), object(1)\n","memory usage: 435.2+ KB\n"]}],"source":["air_pol_death_by_age.info()"]},{"cell_type":"markdown","metadata":{"id":"BzeMRE5oa1s-"},"source":["There are no nulls in our sencod data and this is a relief. Finally, we will do some SQL stuffs with our SQL database."]},{"cell_type":"markdown","metadata":{"id":"p9w8nfmca1s-"},"source":["### Creating and Connecting the SQLite Database\n","\n","First of all, since we don't have a database, let's create sqlite database file named \"day2pm.sqlite\" or anything else that you want. But, before we do that, we need to define a function to create a connection to our sqlite database just like below:"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"BFC4Yvmxa1s-"},"outputs":[],"source":["def create_connection(path):\n","    connection=sqlite3.connect(path)\n","    return(connection)\n","\n","connection = create_connection(\"day2pm.sqlite\")"]},{"cell_type":"markdown","metadata":{"id":"vfbW-L0Aa1s_"},"source":["After we create the database and connection, we need to define a function to send our query into the database. This function used for table creation and data insertion process. "]},{"cell_type":"code","execution_count":null,"metadata":{"id":"f2h9Mz0ta1s_"},"outputs":[],"source":["def execute_query(connection, query):\n","    cursor = connection.cursor()\n","    cursor.execute(query)\n","    connection.commit()\n"]},{"cell_type":"markdown","metadata":{"id":"yzi9Em_Wa1s_"},"source":["### Creating Tables\n","\n","Next, we define the DDL query to create the tables. We have two data so we have to make two tables which are air_pollutant_emission and air_pollutant_death_by_age in seperate query. Please adjust the column name with the pandas dataframes columns. You can edit the columns name as comfortable as you. Be carefull of the data type, check the data before you set the data type on the queries. After defining the queries for each table, we call the execute function and input the connection and query as the arguments for creating the tables."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"zKvMsNwsa1tA"},"outputs":[],"source":["# Create air_pollutant_emission table query\n","\n","create_table_1 = ''' \n","CREATE TABLE IF NOT EXISTS air_pollutant_emission (\n","    id INTEGER PRIMARY KEY AUTOINCREMENT,\n","    Entity TEXT NOT NULL,\n","    Year INTEGER,\n","    CO FLOAT,\n","    NOx FLOAT,\n","    Non_Methane_VOCs FLOAT,\n","    PM1_0 FLOAT,\n","    PM2_5 FLOAT,\n","    SO2 FLOAT,\n","    CO_index FLOAT,\n","    NOx_index FLOAT,\n","    Non_Methane_VOCs_index FLOAT,\n","    PM1_0_index FLOAT,\n","    PM2_5_index FLOAT,\n","    SO2_index FLOAT\n",")\n","'''\n","\n","# Create air_pollutant_death_by_age table query\n","\n","create_table_2 = '''\n","CREATE TABLE IF NOT EXISTS air_pollutant_death_by_age (\n","    id INTEGER PRIMARY KEY AUTOINCREMENT,\n","    Entity TEXT NOT NULL,\n","    Year INTEGER,\n","    Indoor_Under_5 INTEGER,\n","    Indoor_5_to_9 INTEGER,\n","    Indoor_10_to_14 INTEGER,\n","    Indoor_15_to_19 INTEGER,\n","    Indoor_20_to_24 INTEGER,\n","    Indoor_25_to_29 INTEGER,\n","    Indoor_30_to_34 INTEGER,\n","    Indoor_35_to_39 INTEGER,\n","    Indoor_40_to_44 INTEGER,\n","    Indoor_45_to_49 INTEGER,\n","    Indoor_50_to_54 INTEGER,\n","    Indoor_55_to_59 INTEGER,\n","    Indoor_60_to_64 INTEGER,\n","    Indoor_65_to_69 INTEGER,\n","    Indoor_70_to_74 INTEGER,\n","    Indoor_75_to_79 INTEGER,\n","    Indoor_80_plus INTEGER,\n","    Outdoor_Under_5 INTEGER,\n","    Outdoor_5_to_9 INTEGER,\n","    Outdoor_10_to_14 INTEGER,\n","    Outdoor_15_to_19 INTEGER,\n","    Outdoor_20_to_24 INTEGER,\n","    Outdoor_25_to_29 INTEGER,\n","    Outdoor_30_to_34 INTEGER,\n","    Outdoor_35_to_39 INTEGER,\n","    Outdoor_40_to_44 INTEGER,\n","    Outdoor_45_to_49 INTEGER,\n","    Outdoor_50_to_54 INTEGER,\n","    Outdoor_55_to_59 INTEGER,\n","    Outdoor_60_to_64 INTEGER,\n","    Outdoor_65_to_69 INTEGER,\n","    Outdoor_70_to_74 INTEGER,\n","    Outdoor_75_to_79 INTEGER,\n","    Outdoor_80_plus INTEGER\n",")\n","'''\n","\n","execute_query(connection, create_table_1)\n","execute_query(connection, create_table_2)"]},{"cell_type":"markdown","metadata":{"id":"8T0GPDJSa1tA"},"source":["### Inserting the Data\n","\n","In previous step, we only created the tables without inputing the data. In this step, we need to insert the data from the dataframes into the database tables. This is a lot of work actually so please be patient and careful. We use for loop to access each row on each dataframes and insert it to the data one by one."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"RfOjoa-da1tB"},"outputs":[],"source":["for i in range(len(air_pollutant_emission)):\n","    dat = air_pollutant_emission.values[i,:]\n","    insert_values_into_table_1 = f'''\n","    INSERT INTO air_pollutant_emission (\n","        Entity, Year, CO, NOx, Non_Methane_VOCs, PM1_0, PM2_5, SO2, CO_index, NOx_index, Non_Methane_VOCs_index, PM1_0_index, PM2_5_index, SO2_index)\n","    VALUES (\"{dat[0]}\", {dat[1]}, {dat[2]}, {dat[3]}, {dat[4]}, {dat[5]}, {dat[6]}, {dat[7]}, {dat[8]}, {dat[9]}, {dat[10]}, {dat[11]}, {dat[12]}, {dat[13]})\n","    '''\n","    execute_query(connection, insert_values_into_table_1)"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"3HDvfzMea1tB"},"outputs":[],"source":["for i in range(len(air_pol_death_by_age)):\n","    dat = air_pol_death_by_age.values[i,:]\n","    insert_values_into_table_2 = f'''\n","    INSERT INTO air_pollutant_death_by_age (\n","        Entity, Year, Indoor_Under_5, Indoor_5_to_9, Indoor_10_to_14, Indoor_15_to_19, Indoor_20_to_24, Indoor_25_to_29, Indoor_30_to_34, Indoor_35_to_39,\n","        Indoor_40_to_44, Indoor_45_to_49, Indoor_50_to_54, Indoor_55_to_59, Indoor_60_to_64, Indoor_65_to_69, Indoor_70_to_74, Indoor_75_to_79, Indoor_80_plus,\n","        Outdoor_Under_5, Outdoor_5_to_9, Outdoor_10_to_14, Outdoor_15_to_19, Outdoor_20_to_24, Outdoor_25_to_29, Outdoor_30_to_34, Outdoor_35_to_39,\n","        Outdoor_40_to_44, Outdoor_45_to_49, Outdoor_50_to_54, Outdoor_55_to_59, Outdoor_60_to_64, Outdoor_65_to_69, Outdoor_70_to_74, Outdoor_75_to_79, Outdoor_80_plus)\n","    \n","    VALUES (\"{dat[0]}\", {dat[1]}, {dat[18]}, {dat[10]}, {dat[2]}, {dat[3]}, {dat[4]}, {dat[5]}, {dat[6]}, {dat[7]}, {dat[8]}, {dat[9]}, {dat[11]},\n","            {dat[12]}, {dat[13]}, {dat[14]}, {dat[15]}, {dat[16]}, {dat[17]}, {dat[35]}, {dat[27]}, {dat[19]}, {dat[20]}, {dat[21]}, {dat[22]},\n","            {dat[23]}, {dat[24]}, {dat[25]}, {dat[26]}, {dat[28]}, {dat[29]}, {dat[30]}, {dat[31]}, {dat[32]}, {dat[33]}, {dat[34]})\n","    '''\n","    execute_query(connection, insert_values_into_table_2)"]},{"cell_type":"markdown","metadata":{"id":"5MqIoxBRa1tC"},"source":["Yeay, we have already transfer our dataframes into sqlite database successfully. To check our data or tables, please run the cell below:"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"gKLmAT1Ma1tC","outputId":"36df94c3-73b5-4941-d9a7-40bbe4f8368a"},"outputs":[{"name":"stdout","output_type":"stream","text":["[('air_pollutant_emission',), ('air_pollutant_death_by_age',)]\n"]}],"source":["cursor = connection.cursor()\n","\n","cursor.execute(\"SELECT name FROM sqlite_sequence;\")\n","print(cursor.fetchall())"]},{"cell_type":"markdown","metadata":{"id":"noim9hria1tC"},"source":["Our tables are successfully created and now we check the data of each table. We will ask Pandas for help."]},{"cell_type":"code","execution_count":null,"metadata":{"id":"84G-ZZWFa1tC","outputId":"c79dc0cf-e9ab-40d7-946f-ffeacf6e660a"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>id</th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>CO</th>\n","      <th>NOx</th>\n","      <th>Non_Methane_VOCs</th>\n","      <th>PM1_0</th>\n","      <th>PM2_5</th>\n","      <th>SO2</th>\n","      <th>CO_index</th>\n","      <th>NOx_index</th>\n","      <th>Non_Methane_VOCs_index</th>\n","      <th>PM1_0_index</th>\n","      <th>PM2_5_index</th>\n","      <th>SO2_index</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>Australia</td>\n","      <td>1990</td>\n","      <td>5729.0</td>\n","      <td>1621.0</td>\n","      <td>1387.0</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>1555.0</td>\n","      <td>100.00</td>\n","      <td>100.00</td>\n","      <td>100.00</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>100.00</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>Australia</td>\n","      <td>1991</td>\n","      <td>5747.0</td>\n","      <td>1596.0</td>\n","      <td>1373.0</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>1598.0</td>\n","      <td>100.33</td>\n","      <td>98.48</td>\n","      <td>98.96</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>102.74</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>Australia</td>\n","      <td>1992</td>\n","      <td>5897.0</td>\n","      <td>1642.0</td>\n","      <td>1374.0</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>1707.0</td>\n","      <td>102.93</td>\n","      <td>101.31</td>\n","      <td>99.08</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>109.76</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>Australia</td>\n","      <td>1993</td>\n","      <td>6122.0</td>\n","      <td>1685.0</td>\n","      <td>1390.0</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>1757.0</td>\n","      <td>106.87</td>\n","      <td>103.97</td>\n","      <td>100.24</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>112.97</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>Australia</td>\n","      <td>1994</td>\n","      <td>6263.0</td>\n","      <td>1687.0</td>\n","      <td>1403.0</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>1819.0</td>\n","      <td>109.33</td>\n","      <td>104.08</td>\n","      <td>101.12</td>\n","      <td>0.0</td>\n","      <td>0.0</td>\n","      <td>116.97</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   id     Entity  Year      CO     NOx  Non_Methane_VOCs  PM1_0  PM2_5  \\\n","0   1  Australia  1990  5729.0  1621.0            1387.0    0.0    0.0   \n","1   2  Australia  1991  5747.0  1596.0            1373.0    0.0    0.0   \n","2   3  Australia  1992  5897.0  1642.0            1374.0    0.0    0.0   \n","3   4  Australia  1993  6122.0  1685.0            1390.0    0.0    0.0   \n","4   5  Australia  1994  6263.0  1687.0            1403.0    0.0    0.0   \n","\n","      SO2  CO_index  NOx_index  Non_Methane_VOCs_index  PM1_0_index  \\\n","0  1555.0    100.00     100.00                  100.00          0.0   \n","1  1598.0    100.33      98.48                   98.96          0.0   \n","2  1707.0    102.93     101.31                   99.08          0.0   \n","3  1757.0    106.87     103.97                  100.24          0.0   \n","4  1819.0    109.33     104.08                  101.12          0.0   \n","\n","   PM2_5_index  SO2_index  \n","0          0.0     100.00  \n","1          0.0     102.74  \n","2          0.0     109.76  \n","3          0.0     112.97  \n","4          0.0     116.97  "]},"execution_count":15,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql_query('select * from air_pollutant_emission limit 5',connection)"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"bl3NbGTNa1tC","outputId":"b4e5b988-9023-4ce7-98e6-b8c22b252238"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>id</th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>Indoor_Under_5</th>\n","      <th>Indoor_5_to_9</th>\n","      <th>Indoor_10_to_14</th>\n","      <th>Indoor_15_to_19</th>\n","      <th>Indoor_20_to_24</th>\n","      <th>Indoor_25_to_29</th>\n","      <th>Indoor_30_to_34</th>\n","      <th>...</th>\n","      <th>Outdoor_35_to_39</th>\n","      <th>Outdoor_40_to_44</th>\n","      <th>Outdoor_45_to_49</th>\n","      <th>Outdoor_50_to_54</th>\n","      <th>Outdoor_55_to_59</th>\n","      <th>Outdoor_60_to_64</th>\n","      <th>Outdoor_65_to_69</th>\n","      <th>Outdoor_70_to_74</th>\n","      <th>Outdoor_75_to_79</th>\n","      <th>Outdoor_80_plus</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>1</td>\n","      <td>Afghanistan</td>\n","      <td>1990</td>\n","      <td>8683</td>\n","      <td>129</td>\n","      <td>78</td>\n","      <td>45</td>\n","      <td>43</td>\n","      <td>235</td>\n","      <td>335</td>\n","      <td>...</td>\n","      <td>355</td>\n","      <td>557</td>\n","      <td>676</td>\n","      <td>790</td>\n","      <td>823</td>\n","      <td>954</td>\n","      <td>1045</td>\n","      <td>890</td>\n","      <td>576</td>\n","      <td>440</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>2</td>\n","      <td>Afghanistan</td>\n","      <td>1995</td>\n","      <td>10062</td>\n","      <td>182</td>\n","      <td>111</td>\n","      <td>65</td>\n","      <td>62</td>\n","      <td>347</td>\n","      <td>480</td>\n","      <td>...</td>\n","      <td>492</td>\n","      <td>754</td>\n","      <td>1068</td>\n","      <td>1269</td>\n","      <td>1331</td>\n","      <td>1385</td>\n","      <td>1477</td>\n","      <td>1376</td>\n","      <td>947</td>\n","      <td>644</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>3</td>\n","      <td>Afghanistan</td>\n","      <td>2000</td>\n","      <td>9980</td>\n","      <td>194</td>\n","      <td>125</td>\n","      <td>76</td>\n","      <td>73</td>\n","      <td>421</td>\n","      <td>592</td>\n","      <td>...</td>\n","      <td>599</td>\n","      <td>907</td>\n","      <td>1237</td>\n","      <td>1637</td>\n","      <td>1670</td>\n","      <td>1695</td>\n","      <td>1661</td>\n","      <td>1583</td>\n","      <td>1201</td>\n","      <td>898</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>4</td>\n","      <td>Afghanistan</td>\n","      <td>2005</td>\n","      <td>12263</td>\n","      <td>381</td>\n","      <td>184</td>\n","      <td>93</td>\n","      <td>90</td>\n","      <td>511</td>\n","      <td>702</td>\n","      <td>...</td>\n","      <td>667</td>\n","      <td>977</td>\n","      <td>1332</td>\n","      <td>1714</td>\n","      <td>1960</td>\n","      <td>1923</td>\n","      <td>1873</td>\n","      <td>1626</td>\n","      <td>1236</td>\n","      <td>1035</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>5</td>\n","      <td>Afghanistan</td>\n","      <td>2006</td>\n","      <td>11593</td>\n","      <td>373</td>\n","      <td>183</td>\n","      <td>92</td>\n","      <td>89</td>\n","      <td>517</td>\n","      <td>714</td>\n","      <td>...</td>\n","      <td>688</td>\n","      <td>1004</td>\n","      <td>1364</td>\n","      <td>1746</td>\n","      <td>2007</td>\n","      <td>2006</td>\n","      <td>1931</td>\n","      <td>1686</td>\n","      <td>1274</td>\n","      <td>1082</td>\n","    </tr>\n","  </tbody>\n","</table>\n","<p>5 rows × 37 columns</p>\n","</div>"],"text/plain":["   id       Entity  Year  Indoor_Under_5  Indoor_5_to_9  Indoor_10_to_14  \\\n","0   1  Afghanistan  1990            8683            129               78   \n","1   2  Afghanistan  1995           10062            182              111   \n","2   3  Afghanistan  2000            9980            194              125   \n","3   4  Afghanistan  2005           12263            381              184   \n","4   5  Afghanistan  2006           11593            373              183   \n","\n","   Indoor_15_to_19  Indoor_20_to_24  Indoor_25_to_29  Indoor_30_to_34  ...  \\\n","0               45               43              235              335  ...   \n","1               65               62              347              480  ...   \n","2               76               73              421              592  ...   \n","3               93               90              511              702  ...   \n","4               92               89              517              714  ...   \n","\n","   Outdoor_35_to_39  Outdoor_40_to_44  Outdoor_45_to_49  Outdoor_50_to_54  \\\n","0               355               557               676               790   \n","1               492               754              1068              1269   \n","2               599               907              1237              1637   \n","3               667               977              1332              1714   \n","4               688              1004              1364              1746   \n","\n","   Outdoor_55_to_59  Outdoor_60_to_64  Outdoor_65_to_69  Outdoor_70_to_74  \\\n","0               823               954              1045               890   \n","1              1331              1385              1477              1376   \n","2              1670              1695              1661              1583   \n","3              1960              1923              1873              1626   \n","4              2007              2006              1931              1686   \n","\n","   Outdoor_75_to_79  Outdoor_80_plus  \n","0               576              440  \n","1               947              644  \n","2              1201              898  \n","3              1236             1035  \n","4              1274             1082  \n","\n","[5 rows x 37 columns]"]},"execution_count":16,"metadata":{},"output_type":"execute_result"}],"source":["pd.read_sql_query('select * from air_pollutant_death_by_age limit 5',connection)"]},{"cell_type":"markdown","metadata":{"id":"k8TcuZiFa1tD"},"source":["Bravo! We did great."]},{"cell_type":"markdown","metadata":{"id":"XwSpNVHDa1tE"},"source":["## Practical Descriptive Statistics Problems"]},{"cell_type":"markdown","metadata":{"id":"NCdbTkd7a1tF"},"source":["Minister of Environment and Foresty of The Republic of Indonesia, Mrs. Siti Nurbaya Bakar assigns a Data Scientist in her ministry which is YOU to give her some insights about the air pollution condition in OECD countries. To make your works easier, She has some questions that you have to based on data that stored in the sqlite database. The questions are:\n","\n","1. Which OECD country that has the 16th-lowest in 1990 on CO level? Could you provide the data?\n","2. If we compare to the question number 1, which OECD country that has the 16th-lowest in 2005? is the answer still the same? You don't need give the data, just the country name. Give me a name.\n","3. Please give me a table contains the SO2 level in 1990 and the last of year record of Poland. Did it increase or decrease?\n","4. Which OECD country(s) that the PM1.0 level is in maximum value before 2000?\n","5. Could you please provide the death breakdown data by age of the top 2 of highest NOx level of OECD countries for Indoor Under 5 by mean, max, and min?\n"]},{"cell_type":"markdown","metadata":{"id":"Fbltk23Ca1tF"},"source":["### Question 1\n","\n","**Which OECD country that has the 16th-lowest in 1990 on CO level? Could you provide the data?**"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"Cb1LRqDra1tF","outputId":"19f08030-3eba-446b-c160-29a11d3e917a"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>CO</th>\n","      <th>rank</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Iceland</td>\n","      <td>1990</td>\n","      <td>59.0</td>\n","      <td>1</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Estonia</td>\n","      <td>1990</td>\n","      <td>215.0</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Slovenia</td>\n","      <td>1990</td>\n","      <td>324.0</td>\n","      <td>3</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Ireland</td>\n","      <td>1990</td>\n","      <td>348.0</td>\n","      <td>4</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Latvia</td>\n","      <td>1990</td>\n","      <td>386.0</td>\n","      <td>5</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>Luxembourg</td>\n","      <td>1990</td>\n","      <td>463.0</td>\n","      <td>6</td>\n","    </tr>\n","    <tr>\n","      <th>6</th>\n","      <td>Slovakia</td>\n","      <td>1990</td>\n","      <td>505.0</td>\n","      <td>7</td>\n","    </tr>\n","    <tr>\n","      <th>7</th>\n","      <td>New Zealand</td>\n","      <td>1990</td>\n","      <td>598.0</td>\n","      <td>8</td>\n","    </tr>\n","    <tr>\n","      <th>8</th>\n","      <td>Finland</td>\n","      <td>1990</td>\n","      <td>709.0</td>\n","      <td>9</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>Denmark</td>\n","      <td>1990</td>\n","      <td>741.0</td>\n","      <td>10</td>\n","    </tr>\n","    <tr>\n","      <th>10</th>\n","      <td>Switzerland</td>\n","      <td>1990</td>\n","      <td>797.0</td>\n","      <td>11</td>\n","    </tr>\n","    <tr>\n","      <th>11</th>\n","      <td>Portugal</td>\n","      <td>1990</td>\n","      <td>801.0</td>\n","      <td>12</td>\n","    </tr>\n","    <tr>\n","      <th>12</th>\n","      <td>Norway</td>\n","      <td>1990</td>\n","      <td>826.0</td>\n","      <td>13</td>\n","    </tr>\n","    <tr>\n","      <th>13</th>\n","      <td>Czech Republic</td>\n","      <td>1990</td>\n","      <td>1028.0</td>\n","      <td>14</td>\n","    </tr>\n","    <tr>\n","      <th>14</th>\n","      <td>Sweden</td>\n","      <td>1990</td>\n","      <td>1104.0</td>\n","      <td>15</td>\n","    </tr>\n","    <tr>\n","      <th>15</th>\n","      <td>Greece</td>\n","      <td>1990</td>\n","      <td>1134.0</td>\n","      <td>16</td>\n","    </tr>\n","    <tr>\n","      <th>16</th>\n","      <td>Netherlands</td>\n","      <td>1990</td>\n","      <td>1134.0</td>\n","      <td>16</td>\n","    </tr>\n","    <tr>\n","      <th>17</th>\n","      <td>Hungary</td>\n","      <td>1990</td>\n","      <td>1217.0</td>\n","      <td>18</td>\n","    </tr>\n","    <tr>\n","      <th>18</th>\n","      <td>Austria</td>\n","      <td>1990</td>\n","      <td>1286.0</td>\n","      <td>19</td>\n","    </tr>\n","    <tr>\n","      <th>19</th>\n","      <td>Belgium</td>\n","      <td>1990</td>\n","      <td>1412.0</td>\n","      <td>20</td>\n","    </tr>\n","    <tr>\n","      <th>20</th>\n","      <td>Turkey</td>\n","      <td>1990</td>\n","      <td>2023.0</td>\n","      <td>21</td>\n","    </tr>\n","    <tr>\n","      <th>21</th>\n","      <td>Poland</td>\n","      <td>1990</td>\n","      <td>3876.0</td>\n","      <td>22</td>\n","    </tr>\n","    <tr>\n","      <th>22</th>\n","      <td>Japan</td>\n","      <td>1990</td>\n","      <td>4507.0</td>\n","      <td>23</td>\n","    </tr>\n","    <tr>\n","      <th>23</th>\n","      <td>Spain</td>\n","      <td>1990</td>\n","      <td>4781.0</td>\n","      <td>24</td>\n","    </tr>\n","    <tr>\n","      <th>24</th>\n","      <td>Australia</td>\n","      <td>1990</td>\n","      <td>5729.0</td>\n","      <td>25</td>\n","    </tr>\n","    <tr>\n","      <th>25</th>\n","      <td>Italy</td>\n","      <td>1990</td>\n","      <td>7258.0</td>\n","      <td>26</td>\n","    </tr>\n","    <tr>\n","      <th>26</th>\n","      <td>United Kingdom</td>\n","      <td>1990</td>\n","      <td>7376.0</td>\n","      <td>27</td>\n","    </tr>\n","    <tr>\n","      <th>27</th>\n","      <td>France</td>\n","      <td>1990</td>\n","      <td>10421.0</td>\n","      <td>28</td>\n","    </tr>\n","    <tr>\n","      <th>28</th>\n","      <td>Canada</td>\n","      <td>1990</td>\n","      <td>12164.0</td>\n","      <td>29</td>\n","    </tr>\n","    <tr>\n","      <th>29</th>\n","      <td>Germany</td>\n","      <td>1990</td>\n","      <td>12498.0</td>\n","      <td>30</td>\n","    </tr>\n","    <tr>\n","      <th>30</th>\n","      <td>United States</td>\n","      <td>1990</td>\n","      <td>129789.0</td>\n","      <td>31</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["            Entity  Year        CO  rank\n","0          Iceland  1990      59.0     1\n","1          Estonia  1990     215.0     2\n","2         Slovenia  1990     324.0     3\n","3          Ireland  1990     348.0     4\n","4           Latvia  1990     386.0     5\n","5       Luxembourg  1990     463.0     6\n","6         Slovakia  1990     505.0     7\n","7      New Zealand  1990     598.0     8\n","8          Finland  1990     709.0     9\n","9          Denmark  1990     741.0    10\n","10     Switzerland  1990     797.0    11\n","11        Portugal  1990     801.0    12\n","12          Norway  1990     826.0    13\n","13  Czech Republic  1990    1028.0    14\n","14          Sweden  1990    1104.0    15\n","15          Greece  1990    1134.0    16\n","16     Netherlands  1990    1134.0    16\n","17         Hungary  1990    1217.0    18\n","18         Austria  1990    1286.0    19\n","19         Belgium  1990    1412.0    20\n","20          Turkey  1990    2023.0    21\n","21          Poland  1990    3876.0    22\n","22           Japan  1990    4507.0    23\n","23           Spain  1990    4781.0    24\n","24       Australia  1990    5729.0    25\n","25           Italy  1990    7258.0    26\n","26  United Kingdom  1990    7376.0    27\n","27          France  1990   10421.0    28\n","28          Canada  1990   12164.0    29\n","29         Germany  1990   12498.0    30\n","30   United States  1990  129789.0    31"]},"execution_count":28,"metadata":{},"output_type":"execute_result"}],"source":["q1_a = '''\n","select Entity, Year, CO,\n","        rank() over(\n","            order by CO asc\n","           ) as rank\n","from air_pollutant_emission\n","where Entity != 'OECD - Total' and Entity != 'Europe' and Year=1990\n","group by Entity, Year, CO;\n","'''\n","pd.read_sql_query(q1_a,connection)"]},{"cell_type":"markdown","metadata":{"id":"8QS3IUYsa1tG"},"source":["We can see that if we use rank() and use order by CO, Greece and The Netherlands have the same rank since they have the same CO level in 1990. So, the answer is both Greece and The Netherlands. Different with rank(), row_number() will give ordered number based on the order and it does not care about the value. So the result is:"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"SWW0upB-a1tG","outputId":"95060795-6d6f-48e3-d9b3-db8dc9d45207"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>CO</th>\n","      <th>rank</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Iceland</td>\n","      <td>1990</td>\n","      <td>59.0</td>\n","      <td>1</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Estonia</td>\n","      <td>1990</td>\n","      <td>215.0</td>\n","      <td>2</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>Slovenia</td>\n","      <td>1990</td>\n","      <td>324.0</td>\n","      <td>3</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Ireland</td>\n","      <td>1990</td>\n","      <td>348.0</td>\n","      <td>4</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Latvia</td>\n","      <td>1990</td>\n","      <td>386.0</td>\n","      <td>5</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>Luxembourg</td>\n","      <td>1990</td>\n","      <td>463.0</td>\n","      <td>6</td>\n","    </tr>\n","    <tr>\n","      <th>6</th>\n","      <td>Slovakia</td>\n","      <td>1990</td>\n","      <td>505.0</td>\n","      <td>7</td>\n","    </tr>\n","    <tr>\n","      <th>7</th>\n","      <td>New Zealand</td>\n","      <td>1990</td>\n","      <td>598.0</td>\n","      <td>8</td>\n","    </tr>\n","    <tr>\n","      <th>8</th>\n","      <td>Finland</td>\n","      <td>1990</td>\n","      <td>709.0</td>\n","      <td>9</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>Denmark</td>\n","      <td>1990</td>\n","      <td>741.0</td>\n","      <td>10</td>\n","    </tr>\n","    <tr>\n","      <th>10</th>\n","      <td>Switzerland</td>\n","      <td>1990</td>\n","      <td>797.0</td>\n","      <td>11</td>\n","    </tr>\n","    <tr>\n","      <th>11</th>\n","      <td>Portugal</td>\n","      <td>1990</td>\n","      <td>801.0</td>\n","      <td>12</td>\n","    </tr>\n","    <tr>\n","      <th>12</th>\n","      <td>Norway</td>\n","      <td>1990</td>\n","      <td>826.0</td>\n","      <td>13</td>\n","    </tr>\n","    <tr>\n","      <th>13</th>\n","      <td>Czech Republic</td>\n","      <td>1990</td>\n","      <td>1028.0</td>\n","      <td>14</td>\n","    </tr>\n","    <tr>\n","      <th>14</th>\n","      <td>Sweden</td>\n","      <td>1990</td>\n","      <td>1104.0</td>\n","      <td>15</td>\n","    </tr>\n","    <tr>\n","      <th>15</th>\n","      <td>Greece</td>\n","      <td>1990</td>\n","      <td>1134.0</td>\n","      <td>16</td>\n","    </tr>\n","    <tr>\n","      <th>16</th>\n","      <td>Netherlands</td>\n","      <td>1990</td>\n","      <td>1134.0</td>\n","      <td>17</td>\n","    </tr>\n","    <tr>\n","      <th>17</th>\n","      <td>Hungary</td>\n","      <td>1990</td>\n","      <td>1217.0</td>\n","      <td>18</td>\n","    </tr>\n","    <tr>\n","      <th>18</th>\n","      <td>Austria</td>\n","      <td>1990</td>\n","      <td>1286.0</td>\n","      <td>19</td>\n","    </tr>\n","    <tr>\n","      <th>19</th>\n","      <td>Belgium</td>\n","      <td>1990</td>\n","      <td>1412.0</td>\n","      <td>20</td>\n","    </tr>\n","    <tr>\n","      <th>20</th>\n","      <td>Turkey</td>\n","      <td>1990</td>\n","      <td>2023.0</td>\n","      <td>21</td>\n","    </tr>\n","    <tr>\n","      <th>21</th>\n","      <td>Poland</td>\n","      <td>1990</td>\n","      <td>3876.0</td>\n","      <td>22</td>\n","    </tr>\n","    <tr>\n","      <th>22</th>\n","      <td>Japan</td>\n","      <td>1990</td>\n","      <td>4507.0</td>\n","      <td>23</td>\n","    </tr>\n","    <tr>\n","      <th>23</th>\n","      <td>Spain</td>\n","      <td>1990</td>\n","      <td>4781.0</td>\n","      <td>24</td>\n","    </tr>\n","    <tr>\n","      <th>24</th>\n","      <td>Australia</td>\n","      <td>1990</td>\n","      <td>5729.0</td>\n","      <td>25</td>\n","    </tr>\n","    <tr>\n","      <th>25</th>\n","      <td>Italy</td>\n","      <td>1990</td>\n","      <td>7258.0</td>\n","      <td>26</td>\n","    </tr>\n","    <tr>\n","      <th>26</th>\n","      <td>United Kingdom</td>\n","      <td>1990</td>\n","      <td>7376.0</td>\n","      <td>27</td>\n","    </tr>\n","    <tr>\n","      <th>27</th>\n","      <td>France</td>\n","      <td>1990</td>\n","      <td>10421.0</td>\n","      <td>28</td>\n","    </tr>\n","    <tr>\n","      <th>28</th>\n","      <td>Canada</td>\n","      <td>1990</td>\n","      <td>12164.0</td>\n","      <td>29</td>\n","    </tr>\n","    <tr>\n","      <th>29</th>\n","      <td>Germany</td>\n","      <td>1990</td>\n","      <td>12498.0</td>\n","      <td>30</td>\n","    </tr>\n","    <tr>\n","      <th>30</th>\n","      <td>United States</td>\n","      <td>1990</td>\n","      <td>129789.0</td>\n","      <td>31</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["            Entity  Year        CO  rank\n","0          Iceland  1990      59.0     1\n","1          Estonia  1990     215.0     2\n","2         Slovenia  1990     324.0     3\n","3          Ireland  1990     348.0     4\n","4           Latvia  1990     386.0     5\n","5       Luxembourg  1990     463.0     6\n","6         Slovakia  1990     505.0     7\n","7      New Zealand  1990     598.0     8\n","8          Finland  1990     709.0     9\n","9          Denmark  1990     741.0    10\n","10     Switzerland  1990     797.0    11\n","11        Portugal  1990     801.0    12\n","12          Norway  1990     826.0    13\n","13  Czech Republic  1990    1028.0    14\n","14          Sweden  1990    1104.0    15\n","15          Greece  1990    1134.0    16\n","16     Netherlands  1990    1134.0    17\n","17         Hungary  1990    1217.0    18\n","18         Austria  1990    1286.0    19\n","19         Belgium  1990    1412.0    20\n","20          Turkey  1990    2023.0    21\n","21          Poland  1990    3876.0    22\n","22           Japan  1990    4507.0    23\n","23           Spain  1990    4781.0    24\n","24       Australia  1990    5729.0    25\n","25           Italy  1990    7258.0    26\n","26  United Kingdom  1990    7376.0    27\n","27          France  1990   10421.0    28\n","28          Canada  1990   12164.0    29\n","29         Germany  1990   12498.0    30\n","30   United States  1990  129789.0    31"]},"execution_count":29,"metadata":{},"output_type":"execute_result"}],"source":["q1_b = '''\n","select Entity, Year, CO,\n","        row_number() over(\n","            order by CO asc\n","           ) as rank\n","       from air_pollutant_emission\n","where Entity != 'OECD - Total' and Entity != 'Europe' and Year=1990\n","group by Entity, Year, CO;\n","'''\n","pd.read_sql_query(q1_b,connection)"]},{"cell_type":"markdown","metadata":{"id":"EYm-bStya1tG"},"source":["If we asked that which country that has the 16th-lowest CO level in 2016, we only have an answer which is Greece based on row_number() calculation."]},{"cell_type":"markdown","metadata":{"id":"mOFLJ6qCa1tH"},"source":["### Question 2\n","\n","**If we compare to the question number 1, which OECD country that has the 16th-lowest in 2005? is the answer still the same? You don't need give the data, just the country name. Give me a name.**\n","\n","Since Mrs. Minister want a name only, we use row_number instead and we do not need Pandas since we do not show the table"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"r90rkNXKa1tH","outputId":"8f6905e7-f340-48cc-9b54-f1089d85d4c3"},"outputs":[{"name":"stdout","output_type":"stream","text":["The 16th-lowest CO level in 2005: Czech Republic\n"]}],"source":["q2 = '''\n","select Entity from (\n","\n","    select Entity,\n","        row_number() over(\n","            order by CO asc\n","           ) as rank\n","    from air_pollutant_emission as ape\n","    where Entity != 'OECD - Total' and Entity != 'Europe' and Year=2005\n","    group by Entity, Year, CO)\n","\n","where rank=16\n",";\n","'''\n","cursor = connection.cursor()\n","cursor.execute(q2)\n","print('The 16th-lowest CO level in 2005:',cursor.fetchall()[0][0])"]},{"cell_type":"markdown","metadata":{"id":"9vDwMNpfa1tH"},"source":["Greece is not longer be the 16th-lowest CO Level in 2005, but Czech Republic instead."]},{"cell_type":"markdown","metadata":{"id":"KbPZAYkla1tH"},"source":["### Question 3\n","\n","**Please give me a table contains the SO2 level in 1990 and the last of year record of Poland. Did it increase or decrease?**"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"9D1K12Xua1tH","outputId":"8a99cbb1-2a46-4be2-989f-f6e6c86cc38a"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>SO2</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Poland</td>\n","      <td>1990</td>\n","      <td>3210.0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Poland</td>\n","      <td>2015</td>\n","      <td>690.0</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["   Entity  Year     SO2\n","0  Poland  1990  3210.0\n","1  Poland  2015   690.0"]},"execution_count":39,"metadata":{},"output_type":"execute_result"}],"source":["q3 = '''\n","select Entity, Year, SO2 from air_pollutant_emission as ape\n","where Entity = 'Poland' and (Year = 1990 or Year = (select max(Year) from air_pollutant_emission where Entity='Poland' ))\n","group by Entity, SO2\n","order by Year\n","'''\n","pd.read_sql_query(q3,connection)"]},{"cell_type":"markdown","metadata":{"id":"cAlb39Kla1tI"},"source":["In 2015, SO2 emission level of Poland is decreased from 1990"]},{"cell_type":"markdown","metadata":{"id":"f7W7tGb6a1tI"},"source":["### Question 4\n","\n","**Which OECD country(s) that the PM1.0 level is in maximum value before 2000?**"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"idgd9wD5a1tI","outputId":"69765b33-d2cb-4d96-868c-7c748196bd6d"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>Year</th>\n","      <th>Max PM1.0</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Australia</td>\n","      <td>1990</td>\n","      <td>0.0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>Austria</td>\n","      <td>1990</td>\n","      <td>40.0</td>\n","    </tr>\n","    <tr>\n","      <th>2</th>\n","      <td>France</td>\n","      <td>1991</td>\n","      <td>613.0</td>\n","    </tr>\n","    <tr>\n","      <th>3</th>\n","      <td>Germany</td>\n","      <td>1995</td>\n","      <td>329.0</td>\n","    </tr>\n","    <tr>\n","      <th>4</th>\n","      <td>Greece</td>\n","      <td>1990</td>\n","      <td>0.0</td>\n","    </tr>\n","    <tr>\n","      <th>5</th>\n","      <td>Ireland</td>\n","      <td>1990</td>\n","      <td>43.0</td>\n","    </tr>\n","    <tr>\n","      <th>6</th>\n","      <td>Israel</td>\n","      <td>1996</td>\n","      <td>0.0</td>\n","    </tr>\n","    <tr>\n","      <th>7</th>\n","      <td>Italy</td>\n","      <td>1991</td>\n","      <td>278.0</td>\n","    </tr>\n","    <tr>\n","      <th>8</th>\n","      <td>Japan</td>\n","      <td>1990</td>\n","      <td>0.0</td>\n","    </tr>\n","    <tr>\n","      <th>9</th>\n","      <td>Luxembourg</td>\n","      <td>1990</td>\n","      <td>17.0</td>\n","    </tr>\n","    <tr>\n","      <th>10</th>\n","      <td>Netherlands</td>\n","      <td>1990</td>\n","      <td>73.0</td>\n","    </tr>\n","    <tr>\n","      <th>11</th>\n","      <td>New Zealand</td>\n","      <td>1990</td>\n","      <td>0.0</td>\n","    </tr>\n","    <tr>\n","      <th>12</th>\n","      <td>Norway</td>\n","      <td>1997</td>\n","      <td>57.0</td>\n","    </tr>\n","    <tr>\n","      <th>13</th>\n","      <td>Poland</td>\n","      <td>1993</td>\n","      <td>397.0</td>\n","    </tr>\n","    <tr>\n","      <th>14</th>\n","      <td>Portugal</td>\n","      <td>1998</td>\n","      <td>91.0</td>\n","    </tr>\n","    <tr>\n","      <th>15</th>\n","      <td>Slovakia</td>\n","      <td>1990</td>\n","      <td>120.0</td>\n","    </tr>\n","    <tr>\n","      <th>16</th>\n","      <td>Sweden</td>\n","      <td>1990</td>\n","      <td>56.0</td>\n","    </tr>\n","    <tr>\n","      <th>17</th>\n","      <td>Switzerland</td>\n","      <td>1990</td>\n","      <td>27.0</td>\n","    </tr>\n","    <tr>\n","      <th>18</th>\n","      <td>United Kingdom</td>\n","      <td>1990</td>\n","      <td>299.0</td>\n","    </tr>\n","    <tr>\n","      <th>19</th>\n","      <td>United States</td>\n","      <td>1994</td>\n","      <td>25953.0</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["            Entity  Year  Max PM1.0\n","0        Australia  1990        0.0\n","1          Austria  1990       40.0\n","2           France  1991      613.0\n","3          Germany  1995      329.0\n","4           Greece  1990        0.0\n","5          Ireland  1990       43.0\n","6           Israel  1996        0.0\n","7            Italy  1991      278.0\n","8            Japan  1990        0.0\n","9       Luxembourg  1990       17.0\n","10     Netherlands  1990       73.0\n","11     New Zealand  1990        0.0\n","12          Norway  1997       57.0\n","13          Poland  1993      397.0\n","14        Portugal  1998       91.0\n","15        Slovakia  1990      120.0\n","16          Sweden  1990       56.0\n","17     Switzerland  1990       27.0\n","18  United Kingdom  1990      299.0\n","19   United States  1994    25953.0"]},"execution_count":41,"metadata":{},"output_type":"execute_result"}],"source":["q4='''\n","select * from (select Entity, Year, max(PM1_0) as \"Max PM1.0\" from air_pollutant_emission as ape\n","where Entity!=\"Europe\" and Entity!=\"OECD - Total\" group by Entity)\n","where Year < 2000\n","'''\n","pd.read_sql_query(q4,connection)"]},{"cell_type":"markdown","metadata":{"id":"CsRi52mua1tI"},"source":["### Question 5\n","\n","**Could you please provide the death breakdown data by age of the top 2 of highest NOx level of OECD countries in 2015 for Indoor Under 5 by mean, maximum, and minimum?**"]},{"cell_type":"code","execution_count":null,"metadata":{"id":"UgazHhZua1tI","outputId":"ae06258d-c37b-4260-c79a-92f61957d416"},"outputs":[{"data":{"text/html":["<div>\n","<style scoped>\n","    .dataframe tbody tr th:only-of-type {\n","        vertical-align: middle;\n","    }\n","\n","    .dataframe tbody tr th {\n","        vertical-align: top;\n","    }\n","\n","    .dataframe thead th {\n","        text-align: right;\n","    }\n","</style>\n","<table border=\"1\" class=\"dataframe\">\n","  <thead>\n","    <tr style=\"text-align: right;\">\n","      <th></th>\n","      <th>Entity</th>\n","      <th>mean</th>\n","      <th>maximum</th>\n","      <th>minimum</th>\n","    </tr>\n","  </thead>\n","  <tbody>\n","    <tr>\n","      <th>0</th>\n","      <td>Australia</td>\n","      <td>0.000000</td>\n","      <td>0</td>\n","      <td>0</td>\n","    </tr>\n","    <tr>\n","      <th>1</th>\n","      <td>United States</td>\n","      <td>1.285714</td>\n","      <td>2</td>\n","      <td>1</td>\n","    </tr>\n","  </tbody>\n","</table>\n","</div>"],"text/plain":["          Entity      mean  maximum  minimum\n","0      Australia  0.000000        0        0\n","1  United States  1.285714        2        1"]},"execution_count":44,"metadata":{},"output_type":"execute_result"}],"source":["q5 = '''\n","select dat.Entity, avg(apda.Indoor_Under_5) as mean,\n","       max(apda.Indoor_Under_5) as maximum,\n","       min(apda.Indoor_Under_5) as minimum\n","from (select Entity,NOx,rank() over(order by NOx desc) as rank\n","        from air_pollutant_emission\n","        where Entity!=\"Europe\" and Entity!=\"OECD - Total\" and Year=2015\n","        group by Entity, NOx limit 2) as dat\n","join air_pollutant_death_by_age as apda on dat.Entity = apda.Entity\n","group by dat.Entity;\n","'''\n","\n","pd.read_sql_query(q5,connection)"]}],"metadata":{"interpreter":{"hash":"628d2edd94ea9a73662106ab40c62d215abcc5c1c569c1a1997e7af614d5b96a"},"kernelspec":{"display_name":"Python 3.9.5 64-bit ('base': conda)","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.9.5"},"orig_nbformat":4,"colab":{"name":"Salinan dari d2pm.ipynb","provenance":[{"file_id":"https://github.com/ardhiraka/FSDS_Guidelines/blob/master/p0/w3/d2pm.ipynb","timestamp":1654216045674}]}},"nbformat":4,"nbformat_minor":0}