AirBnb_SQL2.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [
        "Pg-a7-xULZ7m"
      ],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/IsiashaG/SLPInTech/blob/main/AirBnb_SQL2.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "> 1. DUPLICATE THIS COLAB TO START WORKING ON IT. Using File > Save a copy to drive.\n",
        "> 2. SHARE SETTINGS: In the new notebook, set the sharing settings to \"Anyone with the link\" can be a \"Commenter\" by clicking \"Share\" on the top right corner.\n",
        "\n",
        "<center>\n",
        "<img src=https://live-production.wcms.abc-cdn.net.au/4d72b144a5661c8423472b84e0b11720 width=\"400\"></center><br />\n",
        "\n",
        "\n",
        "# Week 2: Summarize data of Airbnb Sydney!\n",
        "\n",
        "Welcome to the project for week 2 of our *SQL Crash Course*! To earn your certificate in this class, you must submit both assignments. If you need any help, post in the questions channel on Slack or attend Office Hours. We're here to help you!\n",
        "\n",
        "This week, we went over ways to aggregate your data and learned other intricacies of SQL. We will implement these concepts in this week's project, but as you know, with a slight twist. You are still the General Manager at Airbnb Sydney, and you'll again encounter some scenarios that you'll solve by using SQL!"
      ],
      "metadata": {
        "id": "yVrZZ06OG2LW"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Prerequisite configuration\n",
        "Below we install the software required to run this project. Please make sure to **RUN IT** by clicking on the play-button icon when you hover on the empty square bracket. And feel free to ignore the content of these two hidden cells.\n",
        "\n",
        "IMPORTANT: These cells may have to be rerun every time you are away from the notebook a long time or access notebook on a different browser or a different laptop. If you see an error that says \"NameError: name 'run' is not defined\" you need to run these two hidden cells again."
      ],
      "metadata": {
        "id": "Pg-a7-xULZ7m"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "gUx2DQloGAzk"
      },
      "outputs": [],
      "source": [
        "%%capture\n",
        "!pip install git+https://github.com/sb2nov/sql-cc.git"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "from IPython.display import display, HTML\n",
        "import sqlcc\n",
        "from sqlcc import check\n",
        "\n",
        "# Show all the rows (instead of only a few)\n",
        "pd.set_option(\"display.max_rows\", None)\n",
        "\n",
        "# Set precision to max 2 decimals\n",
        "pd.set_option('precision', 2)\n",
        "\n",
        "# Set CSS Style for Table\n",
        "# Make it work with night & light mode\n",
        "# - Alternating rows\n",
        "# - th elements\n",
        "# - td elements\n",
        "css_style = '''\n",
        "<style>\n",
        "  html {\n",
        "    --td-font-color: black;\n",
        "    --font-color: black;\n",
        "    --background-color: #e0e0e0;\n",
        "  }\n",
        "  html[theme=dark] {\n",
        "    --td-font-color: white;\n",
        "    --font-color: black;\n",
        "    --background-color: #6688ff;\n",
        "  }\n",
        "  th {\n",
        "    background: #fbd44c;\n",
        "    color: var(--font-color);\n",
        "    font-size: 16px;\n",
        "    text-align: center;\n",
        "    font-weight: bold;\n",
        "  }\n",
        "  tr:nth-child(even) {\n",
        "    background-color: var(--background-color);\n",
        "    color: var(--font-color);\n",
        "  }\n",
        "  td {\n",
        "    font-size: 14px;\n",
        "    color: var(--td-font-color);\n",
        "  }\n",
        "</style>\n",
        "'''\n",
        "\n",
        "\n",
        "def run(sql_query):\n",
        "  df = sqlcc.run(sql_query)\n",
        "  \n",
        "  # Puts the scrollbar next to the DataFrame\n",
        "  display(HTML(css_style + \n",
        "               \"<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid;\" + \n",
        "               \" border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'>\" +\n",
        "               df.style.render() +\n",
        "               \"</div>\"))"
      ],
      "metadata": {
        "id": "n-vdL9gWT2rm"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Introduction\n",
        "\n",
        "As the General Manager for the Sydney area at Airbnb, you want to know more about the different listings of houses you onboarded. You have access to information regarding the **listings**, **neighbourhoods**, **reviews**, and **calendar**. Each of these have their own separate table, and follow the structure as shown in the week 1 project:\n",
        "\n",
        "<img src=\"https://corise.com/static/course/sql-crash-course/assets/cl9owz44x005e13a00fb5fhl0/Screen Shot 2022-10-25 at 5.42.23 PM.png\" height=\"450\">\n",
        "\n",
        "## Running and Testing Queries (Gentle reminder!)\n",
        "\n",
        "To access the data contained in these four previously mentioned tables, you'll need to write SQL between the triple quotation marks, like:\n",
        "\n",
        "```\n",
        "query = \"\"\"\n",
        "SELECT * FROM neighbourhoods\n",
        "\"\"\"\n",
        "run(query)\n",
        "check(q2_1_1 = query)\n",
        "```\n",
        "\n",
        "Then that SQL is pulled through a `run()`-command and a `check()`-command so  you can see the output of the SQL query and whether you wrote the expected query. You can see this after the output. If you've done it correctly, you'll see **\"Your SQL query is correct!\"**."
      ],
      "metadata": {
        "id": "XC-fNjXA_40y"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_1_1\n",
        "query = \"SELECT * FROM neighbourhoods\"\n",
        "\n",
        "run(query)\n",
        "check(q2_1_1 = query)"
      ],
      "metadata": {
        "id": "JHUQ1GEG_wm1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 554
        },
        "outputId": "68cfc759-edea-4e82-d9db-8f378addb6d8"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_b8bcb_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >neighbourhood_id</th>\n",
              "      <th class=\"col_heading level0 col1\" >neighbourhood</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_b8bcb_row0_col0\" class=\"data row0 col0\" >0</td>\n",
              "      <td id=\"T_b8bcb_row0_col1\" class=\"data row0 col1\" >Ashfield</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_b8bcb_row1_col0\" class=\"data row1 col0\" >1</td>\n",
              "      <td id=\"T_b8bcb_row1_col1\" class=\"data row1 col1\" >Auburn</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_b8bcb_row2_col0\" class=\"data row2 col0\" >2</td>\n",
              "      <td id=\"T_b8bcb_row2_col1\" class=\"data row2 col1\" >Bankstown</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_b8bcb_row3_col0\" class=\"data row3 col0\" >3</td>\n",
              "      <td id=\"T_b8bcb_row3_col1\" class=\"data row3 col1\" >Blacktown</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_b8bcb_row4_col0\" class=\"data row4 col0\" >4</td>\n",
              "      <td id=\"T_b8bcb_row4_col1\" class=\"data row4 col1\" >Botany Bay</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_b8bcb_row5_col0\" class=\"data row5 col0\" >5</td>\n",
              "      <td id=\"T_b8bcb_row5_col1\" class=\"data row5 col1\" >Burwood</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_b8bcb_row6_col0\" class=\"data row6 col0\" >6</td>\n",
              "      <td id=\"T_b8bcb_row6_col1\" class=\"data row6 col1\" >Camden</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
              "      <td id=\"T_b8bcb_row7_col0\" class=\"data row7 col0\" >7</td>\n",
              "      <td id=\"T_b8bcb_row7_col1\" class=\"data row7 col1\" >Campbelltown</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
              "      <td id=\"T_b8bcb_row8_col0\" class=\"data row8 col0\" >8</td>\n",
              "      <td id=\"T_b8bcb_row8_col1\" class=\"data row8 col1\" >Canada Bay</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
              "      <td id=\"T_b8bcb_row9_col0\" class=\"data row9 col0\" >9</td>\n",
              "      <td id=\"T_b8bcb_row9_col1\" class=\"data row9 col1\" >Canterbury</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
              "      <td id=\"T_b8bcb_row10_col0\" class=\"data row10 col0\" >10</td>\n",
              "      <td id=\"T_b8bcb_row10_col1\" class=\"data row10 col1\" >City Of Kogarah</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
              "      <td id=\"T_b8bcb_row11_col0\" class=\"data row11 col0\" >11</td>\n",
              "      <td id=\"T_b8bcb_row11_col1\" class=\"data row11 col1\" >Fairfield</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
              "      <td id=\"T_b8bcb_row12_col0\" class=\"data row12 col0\" >12</td>\n",
              "      <td id=\"T_b8bcb_row12_col1\" class=\"data row12 col1\" >Holroyd</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
              "      <td id=\"T_b8bcb_row13_col0\" class=\"data row13 col0\" >13</td>\n",
              "      <td id=\"T_b8bcb_row13_col1\" class=\"data row13 col1\" >Hornsby</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
              "      <td id=\"T_b8bcb_row14_col0\" class=\"data row14 col0\" >14</td>\n",
              "      <td id=\"T_b8bcb_row14_col1\" class=\"data row14 col1\" >Hunters Hill</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
              "      <td id=\"T_b8bcb_row15_col0\" class=\"data row15 col0\" >15</td>\n",
              "      <td id=\"T_b8bcb_row15_col1\" class=\"data row15 col1\" >Hurstville</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
              "      <td id=\"T_b8bcb_row16_col0\" class=\"data row16 col0\" >16</td>\n",
              "      <td id=\"T_b8bcb_row16_col1\" class=\"data row16 col1\" >Ku-Ring-Gai</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
              "      <td id=\"T_b8bcb_row17_col0\" class=\"data row17 col0\" >17</td>\n",
              "      <td id=\"T_b8bcb_row17_col1\" class=\"data row17 col1\" >Lane Cove</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
              "      <td id=\"T_b8bcb_row18_col0\" class=\"data row18 col0\" >18</td>\n",
              "      <td id=\"T_b8bcb_row18_col1\" class=\"data row18 col1\" >Leichhardt</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
              "      <td id=\"T_b8bcb_row19_col0\" class=\"data row19 col0\" >19</td>\n",
              "      <td id=\"T_b8bcb_row19_col1\" class=\"data row19 col1\" >Liverpool</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row20\" class=\"row_heading level0 row20\" >20</th>\n",
              "      <td id=\"T_b8bcb_row20_col0\" class=\"data row20 col0\" >20</td>\n",
              "      <td id=\"T_b8bcb_row20_col1\" class=\"data row20 col1\" >Manly</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row21\" class=\"row_heading level0 row21\" >21</th>\n",
              "      <td id=\"T_b8bcb_row21_col0\" class=\"data row21 col0\" >21</td>\n",
              "      <td id=\"T_b8bcb_row21_col1\" class=\"data row21 col1\" >Marrickville</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row22\" class=\"row_heading level0 row22\" >22</th>\n",
              "      <td id=\"T_b8bcb_row22_col0\" class=\"data row22 col0\" >22</td>\n",
              "      <td id=\"T_b8bcb_row22_col1\" class=\"data row22 col1\" >Mosman</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row23\" class=\"row_heading level0 row23\" >23</th>\n",
              "      <td id=\"T_b8bcb_row23_col0\" class=\"data row23 col0\" >23</td>\n",
              "      <td id=\"T_b8bcb_row23_col1\" class=\"data row23 col1\" >North Sydney</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row24\" class=\"row_heading level0 row24\" >24</th>\n",
              "      <td id=\"T_b8bcb_row24_col0\" class=\"data row24 col0\" >24</td>\n",
              "      <td id=\"T_b8bcb_row24_col1\" class=\"data row24 col1\" >Parramatta</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row25\" class=\"row_heading level0 row25\" >25</th>\n",
              "      <td id=\"T_b8bcb_row25_col0\" class=\"data row25 col0\" >25</td>\n",
              "      <td id=\"T_b8bcb_row25_col1\" class=\"data row25 col1\" >Penrith</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row26\" class=\"row_heading level0 row26\" >26</th>\n",
              "      <td id=\"T_b8bcb_row26_col0\" class=\"data row26 col0\" >26</td>\n",
              "      <td id=\"T_b8bcb_row26_col1\" class=\"data row26 col1\" >Pittwater</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row27\" class=\"row_heading level0 row27\" >27</th>\n",
              "      <td id=\"T_b8bcb_row27_col0\" class=\"data row27 col0\" >27</td>\n",
              "      <td id=\"T_b8bcb_row27_col1\" class=\"data row27 col1\" >Randwick</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row28\" class=\"row_heading level0 row28\" >28</th>\n",
              "      <td id=\"T_b8bcb_row28_col0\" class=\"data row28 col0\" >28</td>\n",
              "      <td id=\"T_b8bcb_row28_col1\" class=\"data row28 col1\" >Rockdale</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row29\" class=\"row_heading level0 row29\" >29</th>\n",
              "      <td id=\"T_b8bcb_row29_col0\" class=\"data row29 col0\" >29</td>\n",
              "      <td id=\"T_b8bcb_row29_col1\" class=\"data row29 col1\" >Ryde</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row30\" class=\"row_heading level0 row30\" >30</th>\n",
              "      <td id=\"T_b8bcb_row30_col0\" class=\"data row30 col0\" >30</td>\n",
              "      <td id=\"T_b8bcb_row30_col1\" class=\"data row30 col1\" >Strathfield</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row31\" class=\"row_heading level0 row31\" >31</th>\n",
              "      <td id=\"T_b8bcb_row31_col0\" class=\"data row31 col0\" >31</td>\n",
              "      <td id=\"T_b8bcb_row31_col1\" class=\"data row31 col1\" >Sutherland Shire</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row32\" class=\"row_heading level0 row32\" >32</th>\n",
              "      <td id=\"T_b8bcb_row32_col0\" class=\"data row32 col0\" >32</td>\n",
              "      <td id=\"T_b8bcb_row32_col1\" class=\"data row32 col1\" >Sydney</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row33\" class=\"row_heading level0 row33\" >33</th>\n",
              "      <td id=\"T_b8bcb_row33_col0\" class=\"data row33 col0\" >33</td>\n",
              "      <td id=\"T_b8bcb_row33_col1\" class=\"data row33 col1\" >The Hills Shire</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row34\" class=\"row_heading level0 row34\" >34</th>\n",
              "      <td id=\"T_b8bcb_row34_col0\" class=\"data row34 col0\" >34</td>\n",
              "      <td id=\"T_b8bcb_row34_col1\" class=\"data row34 col1\" >Warringah</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row35\" class=\"row_heading level0 row35\" >35</th>\n",
              "      <td id=\"T_b8bcb_row35_col0\" class=\"data row35 col0\" >35</td>\n",
              "      <td id=\"T_b8bcb_row35_col1\" class=\"data row35 col1\" >Waverley</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row36\" class=\"row_heading level0 row36\" >36</th>\n",
              "      <td id=\"T_b8bcb_row36_col0\" class=\"data row36 col0\" >36</td>\n",
              "      <td id=\"T_b8bcb_row36_col1\" class=\"data row36 col1\" >Willoughby</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_b8bcb_level0_row37\" class=\"row_heading level0 row37\" >37</th>\n",
              "      <td id=\"T_b8bcb_row37_col0\" class=\"data row37 col0\" >37</td>\n",
              "      <td id=\"T_b8bcb_row37_col1\" class=\"data row37 col1\" >Woollahra</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "This vertically-scrollable table displays all of the data contained in the listings table. Feel free to explore all four tables again to familiarize yourself with the data.\n",
        "\n",
        "## Aggregation adventures\n",
        "\n",
        "As General manager for Airbnb Sydney, you need to make data-driven decisions about your region. You've already had many encounters with hosts calling in, and you anticipate many more scenarios that will require you to use SQL in the future. Let's work through some of these scenarios.\n",
        "\n",
        "---\n",
        "\n",
        "## Part 1: Printing a list of hosts whose names start with the letters Jos...\n",
        "\n",
        "Rosa is a stellar support person who works on your team at Airbnb Sydney. She is often the first person who people speak with when they call the Airbnb Sydney office. Rosa recently got a call from a host who had experienced some issues, which she was able to successfully resolve. It's now a week later, and she would like to follow-up with the host to see if they have encountered any additional problems. Unfortunately, Rosa didn't write down the *host_id* and only remembers that the host's name started with \"Jos\". Upset, Rosa asks if you can write a query that matches this description?"
      ],
      "metadata": {
        "id": "RTnUtO7qB5OK"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_2_1\n",
        "# Instructions: \n",
        "# 1/ Fetch all columns from the listings table\n",
        "# 2/ Make sure you filtered the data to names that start with Jos\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT * \n",
        "FROM listings\n",
        "WHERE host LIKE 'Jos%'\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_2_1 = query)"
      ],
      "metadata": {
        "id": "zy7vvwO-B4f2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 140
        },
        "outputId": "6f5cec2f-da6c-447b-ae96-ccf56c48057f"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_49a77_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >listing_id</th>\n",
              "      <th class=\"col_heading level0 col1\" >listing</th>\n",
              "      <th class=\"col_heading level0 col2\" >host_id</th>\n",
              "      <th class=\"col_heading level0 col3\" >host</th>\n",
              "      <th class=\"col_heading level0 col4\" >neighbourhood_id</th>\n",
              "      <th class=\"col_heading level0 col5\" >room_type</th>\n",
              "      <th class=\"col_heading level0 col6\" >price_in_dollar</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_49a77_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_49a77_row0_col0\" class=\"data row0 col0\" >22296011</td>\n",
              "      <td id=\"T_49a77_row0_col1\" class=\"data row0 col1\" >Large private room on Camperdown park & Newtown</td>\n",
              "      <td id=\"T_49a77_row0_col2\" class=\"data row0 col2\" >10873080</td>\n",
              "      <td id=\"T_49a77_row0_col3\" class=\"data row0 col3\" >Joshua</td>\n",
              "      <td id=\"T_49a77_row0_col4\" class=\"data row0 col4\" >21</td>\n",
              "      <td id=\"T_49a77_row0_col5\" class=\"data row0 col5\" >Private room</td>\n",
              "      <td id=\"T_49a77_row0_col6\" class=\"data row0 col6\" >40.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Before Rosa follows up with the host, she wants to make sure that she is prepared for the call. Please write a SQL query that will give Rosa the following info information about the host:\n",
        "- the neighbourhood name\n",
        "- the availability of the room on the calendar\n",
        "- if there are any reviews written for the room\n",
        "\n",
        "After running these queries, please write a short summary of your findings."
      ],
      "metadata": {
        "id": "gk8tpxnDM9PH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_2_2a\n",
        "# Instructions: \n",
        "# 1/ Fetch all columns from the neighbourhoods table\n",
        "# 2/ Make sure you filtered the data on the \n",
        "# neighbourhood id of the host name starting with Jos...\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM neighbourhoods\n",
        "WHERE neighbourhood_id IN \n",
        "    (SELECT neighbourhood_id \n",
        "     FROM listings \n",
        "     WHERE host LIKE 'Jos%')\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_2_2a = query)"
      ],
      "metadata": {
        "id": "3d3ZR1BIM57-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 140
        },
        "outputId": "57aef6f7-155d-460a-97c6-daa801f33a68"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_6d060_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >neighbourhood_id</th>\n",
              "      <th class=\"col_heading level0 col1\" >neighbourhood</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_6d060_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_6d060_row0_col0\" class=\"data row0 col0\" >21</td>\n",
              "      <td id=\"T_6d060_row0_col1\" class=\"data row0 col1\" >Marrickville</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_2_2b\n",
        "# Instructions: \n",
        "# 1/ Fetch all columns from the calendar table\n",
        "# 2/ Make sure you filtered the data on the \n",
        "# listing id of the host name starting with Jos...\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM calendar\n",
        "WHERE listing_id IN \n",
        "    (SELECT listing_id \n",
        "     FROM listings \n",
        "     WHERE host LIKE 'Jos%')\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_2_2b = query)"
      ],
      "metadata": {
        "id": "vihX4ewsNm6g",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 283
        },
        "outputId": "ceb88051-e848-4ce2-bd91-f7541a572710"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_50f8d_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >listing_id</th>\n",
              "      <th class=\"col_heading level0 col1\" >date</th>\n",
              "      <th class=\"col_heading level0 col2\" >available</th>\n",
              "      <th class=\"col_heading level0 col3\" >price_in_dollar</th>\n",
              "      <th class=\"col_heading level0 col4\" >minimum_nights</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_50f8d_row0_col0\" class=\"data row0 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row0_col1\" class=\"data row0 col1\" >2022-09-09T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row0_col2\" class=\"data row0 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row0_col3\" class=\"data row0 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row0_col4\" class=\"data row0 col4\" >90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_50f8d_row1_col0\" class=\"data row1 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row1_col1\" class=\"data row1 col1\" >2022-09-10T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row1_col2\" class=\"data row1 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row1_col3\" class=\"data row1 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row1_col4\" class=\"data row1 col4\" >90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_50f8d_row2_col0\" class=\"data row2 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row2_col1\" class=\"data row2 col1\" >2022-09-11T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row2_col2\" class=\"data row2 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row2_col3\" class=\"data row2 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row2_col4\" class=\"data row2 col4\" >90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_50f8d_row3_col0\" class=\"data row3 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row3_col1\" class=\"data row3 col1\" >2022-09-12T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row3_col2\" class=\"data row3 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row3_col3\" class=\"data row3 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row3_col4\" class=\"data row3 col4\" >90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_50f8d_row4_col0\" class=\"data row4 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row4_col1\" class=\"data row4 col1\" >2022-09-13T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row4_col2\" class=\"data row4 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row4_col3\" class=\"data row4 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row4_col4\" class=\"data row4 col4\" >90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_50f8d_row5_col0\" class=\"data row5 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row5_col1\" class=\"data row5 col1\" >2022-09-14T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row5_col2\" class=\"data row5 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row5_col3\" class=\"data row5 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row5_col4\" class=\"data row5 col4\" >90</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_50f8d_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_50f8d_row6_col0\" class=\"data row6 col0\" >22296011</td>\n",
              "      <td id=\"T_50f8d_row6_col1\" class=\"data row6 col1\" >2022-09-15T00:00:00Z</td>\n",
              "      <td id=\"T_50f8d_row6_col2\" class=\"data row6 col2\" >0</td>\n",
              "      <td id=\"T_50f8d_row6_col3\" class=\"data row6 col3\" >40.00</td>\n",
              "      <td id=\"T_50f8d_row6_col4\" class=\"data row6 col4\" >90</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_2_2c\n",
        "# Instructions: \n",
        "# 1/ Fetch all columns from the reviews table\n",
        "# 2/ Make sure you filtered the data on the \n",
        "# listing id of the host name starting with Jos...\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT *\n",
        "FROM reviews\n",
        "WHERE listing_id IN \n",
        "    (SELECT listing_id \n",
        "     FROM listings \n",
        "     WHERE host LIKE 'Jos%')\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_2_2c = query)"
      ],
      "metadata": {
        "id": "EgQiIdPJNnHV",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 116
        },
        "outputId": "96e4f761-a1b7-44ca-b693-6a348bb7d1e7"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_1bbaa_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >listing_id</th>\n",
              "      <th class=\"col_heading level0 col1\" >review_id</th>\n",
              "      <th class=\"col_heading level0 col2\" >date</th>\n",
              "      <th class=\"col_heading level0 col3\" >reviewer_id</th>\n",
              "      <th class=\"col_heading level0 col4\" >reviewer</th>\n",
              "      <th class=\"col_heading level0 col5\" >comments</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "If you've made it this far, you've found that the host's name is \"Joshua.\" He lives in Marrickville, has no availablility all week, and no reviews have been made for his room.\n",
        "\n",
        "If you're stuck, just post in our questions channel on Slack!"
      ],
      "metadata": {
        "id": "h-4YT2ZpOiGR"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "---\n",
        "\n",
        "## Part 2: Airbnb Melbourne called...\n",
        "\n",
        "Airbnb Melbourne has been in operation longer than our office in Sydney. They called to gauge the prices that we have for our listings. They wanted to know the maximum, minimum, and average price for our listings to compare with what they're seeing in their region. Let's write these three queries!"
      ],
      "metadata": {
        "id": "mzYu85zuUVI5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_3_1a\n",
        "# Instructions: \n",
        "# 1/ Fetch price_in_dollar from the listings table\n",
        "# 2/ Get the \"Average\" price across all listings\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT AVG(price_in_dollar)\n",
        " FROM listings\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_3_1a = query)"
      ],
      "metadata": {
        "id": "eA46RTlNVQie",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 140
        },
        "outputId": "dd171305-7fa5-48b9-e5b6-afcdac46b68a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_a5f4b_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >AVG(price_in_dollar)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_a5f4b_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_a5f4b_row0_col0\" class=\"data row0 col0\" >703.57</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_3_1b\n",
        "# Instructions: \n",
        "# 1/ Fetch price_in_dollar from the listings table\n",
        "# 2/ Get the \"Maximum\" price across all listings\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT MAX(price_in_dollar)\n",
        " FROM listings\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_3_1b = query)"
      ],
      "metadata": {
        "id": "4hloPjd-VS40",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 140
        },
        "outputId": "906e2d85-e8f3-41c2-9389-9f44efaa75e4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_2e551_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >MAX(price_in_dollar)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_2e551_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_2e551_row0_col0\" class=\"data row0 col0\" >28613.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_3_1c\n",
        "# Instructions: \n",
        "# 1/ Fetch price_in_dollar from the listings table\n",
        "# 2/ Get the \"Minimum\" price across all listings\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT MIN(price_in_dollar)\n",
        " FROM listings\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_3_1c = query)"
      ],
      "metadata": {
        "id": "6lEEgAojVTYR",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 140
        },
        "outputId": "548a84a6-eede-498b-c995-1c671c0ffe55"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_e5ca2_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >MIN(price_in_dollar)</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_e5ca2_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_e5ca2_row0_col0\" class=\"data row0 col0\" >30.00</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "---\n",
        "\n",
        "## Part 3: Monthly growth goals.\n",
        "\n",
        "I've got headquarters on my back! They keep calling and asking whether we'll meet our monthly growth goals. We need to expand as quickly as possible all across Sydney. Some mentors tell me it's better to focus on places where we are already quite present. But I believe it'll serve us better to be spread out well across Sydney. I'm worried about overrepresentation in certain neighbourhoods, which might anger local officials and even put our jobs at risk.\n",
        "\n",
        "First, let's see if we can get an overview of which neighbourhoods have the most listings. Make use of GROUP BY where we focus on neighbourhood_id and its count."
      ],
      "metadata": {
        "id": "tatbe8pAOCJE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_4_1\n",
        "# Instructions:\n",
        "# 1/ Fetch neighbourhood_id from the listings table and\n",
        "# 2/ the number of listings in each neighbourhood -- Please count the neighbourhood_id column.\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT COUNT(*), neighbourhood_id\n",
        "  FROM listings\n",
        "  GROUP BY neighbourhood_id\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_4_1 = query)"
      ],
      "metadata": {
        "id": "GtiB8SapOBLo",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 590
        },
        "outputId": "e4d4b4ba-18b6-420a-acea-4253dcf7347e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_ef02b_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >COUNT(*)</th>\n",
              "      <th class=\"col_heading level0 col1\" >neighbourhood_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_ef02b_row0_col0\" class=\"data row0 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row0_col1\" class=\"data row0 col1\" >0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_ef02b_row1_col0\" class=\"data row1 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row1_col1\" class=\"data row1 col1\" >1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_ef02b_row2_col0\" class=\"data row2 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row2_col1\" class=\"data row2 col1\" >4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_ef02b_row3_col0\" class=\"data row3 col0\" >2</td>\n",
              "      <td id=\"T_ef02b_row3_col1\" class=\"data row3 col1\" >8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_ef02b_row4_col0\" class=\"data row4 col0\" >2</td>\n",
              "      <td id=\"T_ef02b_row4_col1\" class=\"data row4 col1\" >11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_ef02b_row5_col0\" class=\"data row5 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row5_col1\" class=\"data row5 col1\" >17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_ef02b_row6_col0\" class=\"data row6 col0\" >2</td>\n",
              "      <td id=\"T_ef02b_row6_col1\" class=\"data row6 col1\" >18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
              "      <td id=\"T_ef02b_row7_col0\" class=\"data row7 col0\" >5</td>\n",
              "      <td id=\"T_ef02b_row7_col1\" class=\"data row7 col1\" >20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
              "      <td id=\"T_ef02b_row8_col0\" class=\"data row8 col0\" >2</td>\n",
              "      <td id=\"T_ef02b_row8_col1\" class=\"data row8 col1\" >21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
              "      <td id=\"T_ef02b_row9_col0\" class=\"data row9 col0\" >3</td>\n",
              "      <td id=\"T_ef02b_row9_col1\" class=\"data row9 col1\" >23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
              "      <td id=\"T_ef02b_row10_col0\" class=\"data row10 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row10_col1\" class=\"data row10 col1\" >24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
              "      <td id=\"T_ef02b_row11_col0\" class=\"data row11 col0\" >3</td>\n",
              "      <td id=\"T_ef02b_row11_col1\" class=\"data row11 col1\" >26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
              "      <td id=\"T_ef02b_row12_col0\" class=\"data row12 col0\" >4</td>\n",
              "      <td id=\"T_ef02b_row12_col1\" class=\"data row12 col1\" >27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
              "      <td id=\"T_ef02b_row13_col0\" class=\"data row13 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row13_col1\" class=\"data row13 col1\" >28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
              "      <td id=\"T_ef02b_row14_col0\" class=\"data row14 col0\" >3</td>\n",
              "      <td id=\"T_ef02b_row14_col1\" class=\"data row14 col1\" >29</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
              "      <td id=\"T_ef02b_row15_col0\" class=\"data row15 col0\" >2</td>\n",
              "      <td id=\"T_ef02b_row15_col1\" class=\"data row15 col1\" >31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
              "      <td id=\"T_ef02b_row16_col0\" class=\"data row16 col0\" >10</td>\n",
              "      <td id=\"T_ef02b_row16_col1\" class=\"data row16 col1\" >32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
              "      <td id=\"T_ef02b_row17_col0\" class=\"data row17 col0\" >1</td>\n",
              "      <td id=\"T_ef02b_row17_col1\" class=\"data row17 col1\" >33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
              "      <td id=\"T_ef02b_row18_col0\" class=\"data row18 col0\" >13</td>\n",
              "      <td id=\"T_ef02b_row18_col1\" class=\"data row18 col1\" >35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ef02b_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
              "      <td id=\"T_ef02b_row19_col0\" class=\"data row19 col0\" >5</td>\n",
              "      <td id=\"T_ef02b_row19_col1\" class=\"data row19 col1\" >37</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Hmm, let's make our results more a bit more useful. Order it by count descendingly. Also, which are the neighbourhoods that are least represented?"
      ],
      "metadata": {
        "id": "Ffxa0FIcSPjY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_4_2\n",
        "# Instructions: \n",
        "# 1/ Fetch neighbourhood_id from the listings table\n",
        "# 2/ For the second column get the number of listings in each neighbourhood\n",
        "# 3/ Make sure to order by count descendingly.\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT COUNT(*), neighbourhood_id\n",
        "  FROM listings\n",
        "  GROUP BY neighbourhood_id \n",
        "  ORDER BY COUNT(*) DESC\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_4_2 = query)"
      ],
      "metadata": {
        "id": "6uahuSUPSO_S",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 590
        },
        "outputId": "76b9c03f-9561-4b9c-823c-ef680b9987ff"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_ccdd4_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >COUNT(*)</th>\n",
              "      <th class=\"col_heading level0 col1\" >neighbourhood_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_ccdd4_row0_col0\" class=\"data row0 col0\" >13</td>\n",
              "      <td id=\"T_ccdd4_row0_col1\" class=\"data row0 col1\" >35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_ccdd4_row1_col0\" class=\"data row1 col0\" >10</td>\n",
              "      <td id=\"T_ccdd4_row1_col1\" class=\"data row1 col1\" >32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_ccdd4_row2_col0\" class=\"data row2 col0\" >5</td>\n",
              "      <td id=\"T_ccdd4_row2_col1\" class=\"data row2 col1\" >37</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_ccdd4_row3_col0\" class=\"data row3 col0\" >5</td>\n",
              "      <td id=\"T_ccdd4_row3_col1\" class=\"data row3 col1\" >20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_ccdd4_row4_col0\" class=\"data row4 col0\" >4</td>\n",
              "      <td id=\"T_ccdd4_row4_col1\" class=\"data row4 col1\" >27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_ccdd4_row5_col0\" class=\"data row5 col0\" >3</td>\n",
              "      <td id=\"T_ccdd4_row5_col1\" class=\"data row5 col1\" >29</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_ccdd4_row6_col0\" class=\"data row6 col0\" >3</td>\n",
              "      <td id=\"T_ccdd4_row6_col1\" class=\"data row6 col1\" >26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
              "      <td id=\"T_ccdd4_row7_col0\" class=\"data row7 col0\" >3</td>\n",
              "      <td id=\"T_ccdd4_row7_col1\" class=\"data row7 col1\" >23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
              "      <td id=\"T_ccdd4_row8_col0\" class=\"data row8 col0\" >2</td>\n",
              "      <td id=\"T_ccdd4_row8_col1\" class=\"data row8 col1\" >31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
              "      <td id=\"T_ccdd4_row9_col0\" class=\"data row9 col0\" >2</td>\n",
              "      <td id=\"T_ccdd4_row9_col1\" class=\"data row9 col1\" >21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
              "      <td id=\"T_ccdd4_row10_col0\" class=\"data row10 col0\" >2</td>\n",
              "      <td id=\"T_ccdd4_row10_col1\" class=\"data row10 col1\" >18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
              "      <td id=\"T_ccdd4_row11_col0\" class=\"data row11 col0\" >2</td>\n",
              "      <td id=\"T_ccdd4_row11_col1\" class=\"data row11 col1\" >11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
              "      <td id=\"T_ccdd4_row12_col0\" class=\"data row12 col0\" >2</td>\n",
              "      <td id=\"T_ccdd4_row12_col1\" class=\"data row12 col1\" >8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
              "      <td id=\"T_ccdd4_row13_col0\" class=\"data row13 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row13_col1\" class=\"data row13 col1\" >33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
              "      <td id=\"T_ccdd4_row14_col0\" class=\"data row14 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row14_col1\" class=\"data row14 col1\" >28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
              "      <td id=\"T_ccdd4_row15_col0\" class=\"data row15 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row15_col1\" class=\"data row15 col1\" >24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
              "      <td id=\"T_ccdd4_row16_col0\" class=\"data row16 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row16_col1\" class=\"data row16 col1\" >17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
              "      <td id=\"T_ccdd4_row17_col0\" class=\"data row17 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row17_col1\" class=\"data row17 col1\" >4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
              "      <td id=\"T_ccdd4_row18_col0\" class=\"data row18 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row18_col1\" class=\"data row18 col1\" >1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_ccdd4_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
              "      <td id=\"T_ccdd4_row19_col0\" class=\"data row19 col0\" >1</td>\n",
              "      <td id=\"T_ccdd4_row19_col1\" class=\"data row19 col1\" >0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Looks like we've 20 neighbourhoods in the listings table but quiet a few with really few listings. \n",
        "\n",
        "Are there neighbourhoods with no Airbnb listings?  How do we get all the neighbourhood_id's from neighbourhoods?"
      ],
      "metadata": {
        "id": "O_zBfaS6hSLX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_4_3b\n",
        "# Instructions: \n",
        "# 1/ Fetch neighbourhood_id from the neighbourhoods table\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT neighbourhood_id\n",
        "FROM neighbourhoods\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_4_3b = query)"
      ],
      "metadata": {
        "id": "JqaftXpKnO9j",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 590
        },
        "outputId": "e55dd5c9-07c5-4d92-e5dc-663ff9335e55"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_09210_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >neighbourhood_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_09210_row0_col0\" class=\"data row0 col0\" >0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_09210_row1_col0\" class=\"data row1 col0\" >1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_09210_row2_col0\" class=\"data row2 col0\" >2</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_09210_row3_col0\" class=\"data row3 col0\" >3</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_09210_row4_col0\" class=\"data row4 col0\" >4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_09210_row5_col0\" class=\"data row5 col0\" >5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_09210_row6_col0\" class=\"data row6 col0\" >6</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
              "      <td id=\"T_09210_row7_col0\" class=\"data row7 col0\" >7</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
              "      <td id=\"T_09210_row8_col0\" class=\"data row8 col0\" >8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
              "      <td id=\"T_09210_row9_col0\" class=\"data row9 col0\" >9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
              "      <td id=\"T_09210_row10_col0\" class=\"data row10 col0\" >10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
              "      <td id=\"T_09210_row11_col0\" class=\"data row11 col0\" >11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
              "      <td id=\"T_09210_row12_col0\" class=\"data row12 col0\" >12</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
              "      <td id=\"T_09210_row13_col0\" class=\"data row13 col0\" >13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
              "      <td id=\"T_09210_row14_col0\" class=\"data row14 col0\" >14</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
              "      <td id=\"T_09210_row15_col0\" class=\"data row15 col0\" >15</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
              "      <td id=\"T_09210_row16_col0\" class=\"data row16 col0\" >16</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
              "      <td id=\"T_09210_row17_col0\" class=\"data row17 col0\" >17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
              "      <td id=\"T_09210_row18_col0\" class=\"data row18 col0\" >18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
              "      <td id=\"T_09210_row19_col0\" class=\"data row19 col0\" >19</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row20\" class=\"row_heading level0 row20\" >20</th>\n",
              "      <td id=\"T_09210_row20_col0\" class=\"data row20 col0\" >20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row21\" class=\"row_heading level0 row21\" >21</th>\n",
              "      <td id=\"T_09210_row21_col0\" class=\"data row21 col0\" >21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row22\" class=\"row_heading level0 row22\" >22</th>\n",
              "      <td id=\"T_09210_row22_col0\" class=\"data row22 col0\" >22</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row23\" class=\"row_heading level0 row23\" >23</th>\n",
              "      <td id=\"T_09210_row23_col0\" class=\"data row23 col0\" >23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row24\" class=\"row_heading level0 row24\" >24</th>\n",
              "      <td id=\"T_09210_row24_col0\" class=\"data row24 col0\" >24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row25\" class=\"row_heading level0 row25\" >25</th>\n",
              "      <td id=\"T_09210_row25_col0\" class=\"data row25 col0\" >25</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row26\" class=\"row_heading level0 row26\" >26</th>\n",
              "      <td id=\"T_09210_row26_col0\" class=\"data row26 col0\" >26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row27\" class=\"row_heading level0 row27\" >27</th>\n",
              "      <td id=\"T_09210_row27_col0\" class=\"data row27 col0\" >27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row28\" class=\"row_heading level0 row28\" >28</th>\n",
              "      <td id=\"T_09210_row28_col0\" class=\"data row28 col0\" >28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row29\" class=\"row_heading level0 row29\" >29</th>\n",
              "      <td id=\"T_09210_row29_col0\" class=\"data row29 col0\" >29</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row30\" class=\"row_heading level0 row30\" >30</th>\n",
              "      <td id=\"T_09210_row30_col0\" class=\"data row30 col0\" >30</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row31\" class=\"row_heading level0 row31\" >31</th>\n",
              "      <td id=\"T_09210_row31_col0\" class=\"data row31 col0\" >31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row32\" class=\"row_heading level0 row32\" >32</th>\n",
              "      <td id=\"T_09210_row32_col0\" class=\"data row32 col0\" >32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row33\" class=\"row_heading level0 row33\" >33</th>\n",
              "      <td id=\"T_09210_row33_col0\" class=\"data row33 col0\" >33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row34\" class=\"row_heading level0 row34\" >34</th>\n",
              "      <td id=\"T_09210_row34_col0\" class=\"data row34 col0\" >34</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row35\" class=\"row_heading level0 row35\" >35</th>\n",
              "      <td id=\"T_09210_row35_col0\" class=\"data row35 col0\" >35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row36\" class=\"row_heading level0 row36\" >36</th>\n",
              "      <td id=\"T_09210_row36_col0\" class=\"data row36 col0\" >36</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_09210_level0_row37\" class=\"row_heading level0 row37\" >37</th>\n",
              "      <td id=\"T_09210_row37_col0\" class=\"data row37 col0\" >37</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "We see about 38 neighbourhoods in the neighbourhoods table compared to the 20 in listings. So our analysis shows that some neighbourhoods have few listings or none at all. These might offer interesting new opportunities to explore."
      ],
      "metadata": {
        "id": "xpDZrYvdRE8W"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "---\n",
        "\n",
        "## Part 4: Creating a new (simplified) column\n",
        "\n",
        "Now that we've recruited hosts and have some listings, we want to improve the experience of our customers. We want to make it faster for them to go through listings that might be of interest to them. Some of our customers have a big pockets, and others don't. So we want to make three kinds of classes:\n",
        "- Affordable (0 - 70 dollar per night)\n",
        "- Mid-range (70 - 300 dollar per night)\n",
        "- Expensive (300+ dollar per night)\n",
        "\n",
        "Let's make a SQL case statement to create a new column called \"price_range\", which includes the *listing*, *listing_id*, *host*, and *host_id*."
      ],
      "metadata": {
        "id": "Eap0sr5EPbjm"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_6_1\n",
        "# 1/ Fetch listing, listing_id, host, host_id\n",
        "# from the listings table\n",
        "# 2/ Create a case on price_in_dollar which takes into account:\n",
        "# - Affordable < 70\n",
        "# - Expensive > 300\n",
        "# - Else Mid-range\n",
        "# This we call price_range\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT listing, listing_id, host, host_id,\n",
        "CASE \n",
        "WHEN price_in_dollar < 70 THEN 'Affordable'\n",
        "WHEN price_in_dollar > 300 THEN 'Expensive'\n",
        "ELSE 'Mid-range'\n",
        "END AS price_range\n",
        "FROM listings\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_6_1 = query)"
      ],
      "metadata": {
        "id": "Qrib72XpPbjq",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 590
        },
        "outputId": "2ae2232e-7ddd-47c5-9440-08899c9c7643"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_e2de1_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >listing</th>\n",
              "      <th class=\"col_heading level0 col1\" >listing_id</th>\n",
              "      <th class=\"col_heading level0 col2\" >host</th>\n",
              "      <th class=\"col_heading level0 col3\" >host_id</th>\n",
              "      <th class=\"col_heading level0 col4\" >price_range</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_e2de1_row0_col0\" class=\"data row0 col0\" >Peaceful 1 Bedroom Apartment in Bondi Beach</td>\n",
              "      <td id=\"T_e2de1_row0_col1\" class=\"data row0 col1\" >574105250645758899</td>\n",
              "      <td id=\"T_e2de1_row0_col2\" class=\"data row0 col2\" >Andrew</td>\n",
              "      <td id=\"T_e2de1_row0_col3\" class=\"data row0 col3\" >109067745</td>\n",
              "      <td id=\"T_e2de1_row0_col4\" class=\"data row0 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_e2de1_row1_col0\" class=\"data row1 col0\" >Bondi Vibes - Funky Designer Studio</td>\n",
              "      <td id=\"T_e2de1_row1_col1\" class=\"data row1 col1\" >7874902</td>\n",
              "      <td id=\"T_e2de1_row1_col2\" class=\"data row1 col2\" >Alex White</td>\n",
              "      <td id=\"T_e2de1_row1_col3\" class=\"data row1 col3\" >41506490</td>\n",
              "      <td id=\"T_e2de1_row1_col4\" class=\"data row1 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_e2de1_row2_col0\" class=\"data row2 col0\" >Nice studio close to the beach!</td>\n",
              "      <td id=\"T_e2de1_row2_col1\" class=\"data row2 col1\" >4575789</td>\n",
              "      <td id=\"T_e2de1_row2_col2\" class=\"data row2 col2\" >María</td>\n",
              "      <td id=\"T_e2de1_row2_col3\" class=\"data row2 col3\" >22980172</td>\n",
              "      <td id=\"T_e2de1_row2_col4\" class=\"data row2 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_e2de1_row3_col0\" class=\"data row3 col0\" >Just bring your beach towel</td>\n",
              "      <td id=\"T_e2de1_row3_col1\" class=\"data row3 col1\" >23077495</td>\n",
              "      <td id=\"T_e2de1_row3_col2\" class=\"data row3 col2\" >Gladys</td>\n",
              "      <td id=\"T_e2de1_row3_col3\" class=\"data row3 col3\" >1305312</td>\n",
              "      <td id=\"T_e2de1_row3_col4\" class=\"data row3 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_e2de1_row4_col0\" class=\"data row4 col0\" >Stylish lite 2b+2bth mod secure Beach apt with pkg</td>\n",
              "      <td id=\"T_e2de1_row4_col1\" class=\"data row4 col1\" >657377039990074158</td>\n",
              "      <td id=\"T_e2de1_row4_col2\" class=\"data row4 col2\" >Rick</td>\n",
              "      <td id=\"T_e2de1_row4_col3\" class=\"data row4 col3\" >285488167</td>\n",
              "      <td id=\"T_e2de1_row4_col4\" class=\"data row4 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_e2de1_row5_col0\" class=\"data row5 col0\" >Spacious and clean 2-bedroom apartment in Bondi</td>\n",
              "      <td id=\"T_e2de1_row5_col1\" class=\"data row5 col1\" >53798702</td>\n",
              "      <td id=\"T_e2de1_row5_col2\" class=\"data row5 col2\" >Tiina</td>\n",
              "      <td id=\"T_e2de1_row5_col3\" class=\"data row5 col3\" >244604436</td>\n",
              "      <td id=\"T_e2de1_row5_col4\" class=\"data row5 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_e2de1_row6_col0\" class=\"data row6 col0\" >Bondi Beach Apartment 50m to beach</td>\n",
              "      <td id=\"T_e2de1_row6_col1\" class=\"data row6 col1\" >4344478</td>\n",
              "      <td id=\"T_e2de1_row6_col2\" class=\"data row6 col2\" >Stephen</td>\n",
              "      <td id=\"T_e2de1_row6_col3\" class=\"data row6 col3\" >22553304</td>\n",
              "      <td id=\"T_e2de1_row6_col4\" class=\"data row6 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
              "      <td id=\"T_e2de1_row7_col0\" class=\"data row7 col0\" >Calm & Coastal: Bronte Beach Studio with Parking</td>\n",
              "      <td id=\"T_e2de1_row7_col1\" class=\"data row7 col1\" >48699778</td>\n",
              "      <td id=\"T_e2de1_row7_col2\" class=\"data row7 col2\" >Annie</td>\n",
              "      <td id=\"T_e2de1_row7_col3\" class=\"data row7 col3\" >185783910</td>\n",
              "      <td id=\"T_e2de1_row7_col4\" class=\"data row7 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
              "      <td id=\"T_e2de1_row8_col0\" class=\"data row8 col0\" >Minutes to Bronte beach Ocean views</td>\n",
              "      <td id=\"T_e2de1_row8_col1\" class=\"data row8 col1\" >12072720</td>\n",
              "      <td id=\"T_e2de1_row8_col2\" class=\"data row8 col2\" >Angelika</td>\n",
              "      <td id=\"T_e2de1_row8_col3\" class=\"data row8 col3\" >11745874</td>\n",
              "      <td id=\"T_e2de1_row8_col4\" class=\"data row8 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
              "      <td id=\"T_e2de1_row9_col0\" class=\"data row9 col0\" >Terrace in heart of Bondi Junction</td>\n",
              "      <td id=\"T_e2de1_row9_col1\" class=\"data row9 col1\" >20255786</td>\n",
              "      <td id=\"T_e2de1_row9_col2\" class=\"data row9 col2\" >Astrid And Nick</td>\n",
              "      <td id=\"T_e2de1_row9_col3\" class=\"data row9 col3\" >18901875</td>\n",
              "      <td id=\"T_e2de1_row9_col4\" class=\"data row9 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
              "      <td id=\"T_e2de1_row10_col0\" class=\"data row10 col0\" >Bright stylish home in a great location</td>\n",
              "      <td id=\"T_e2de1_row10_col1\" class=\"data row10 col1\" >22973428</td>\n",
              "      <td id=\"T_e2de1_row10_col2\" class=\"data row10 col2\" >Sian</td>\n",
              "      <td id=\"T_e2de1_row10_col3\" class=\"data row10 col3\" >2537559</td>\n",
              "      <td id=\"T_e2de1_row10_col4\" class=\"data row10 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
              "      <td id=\"T_e2de1_row11_col0\" class=\"data row11 col0\" >Double room available in open and airy Bronte home</td>\n",
              "      <td id=\"T_e2de1_row11_col1\" class=\"data row11 col1\" >22199388</td>\n",
              "      <td id=\"T_e2de1_row11_col2\" class=\"data row11 col2\" >Audrey</td>\n",
              "      <td id=\"T_e2de1_row11_col3\" class=\"data row11 col3\" >14228436</td>\n",
              "      <td id=\"T_e2de1_row11_col4\" class=\"data row11 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
              "      <td id=\"T_e2de1_row12_col0\" class=\"data row12 col0\" >Bondi Beachy - 2 bedroom in the heart of Bondi!</td>\n",
              "      <td id=\"T_e2de1_row12_col1\" class=\"data row12 col1\" >705145817561041469</td>\n",
              "      <td id=\"T_e2de1_row12_col2\" class=\"data row12 col2\" >Bondi Beach Holiday Homes</td>\n",
              "      <td id=\"T_e2de1_row12_col3\" class=\"data row12 col3\" >113874</td>\n",
              "      <td id=\"T_e2de1_row12_col4\" class=\"data row12 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
              "      <td id=\"T_e2de1_row13_col0\" class=\"data row13 col0\" >House in Central Sydney</td>\n",
              "      <td id=\"T_e2de1_row13_col1\" class=\"data row13 col1\" >8925052</td>\n",
              "      <td id=\"T_e2de1_row13_col2\" class=\"data row13 col2\" >Barry</td>\n",
              "      <td id=\"T_e2de1_row13_col3\" class=\"data row13 col3\" >43312353</td>\n",
              "      <td id=\"T_e2de1_row13_col4\" class=\"data row13 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
              "      <td id=\"T_e2de1_row14_col0\" class=\"data row14 col0\" >Central and Sunny Room</td>\n",
              "      <td id=\"T_e2de1_row14_col1\" class=\"data row14 col1\" >11970913</td>\n",
              "      <td id=\"T_e2de1_row14_col2\" class=\"data row14 col2\" >Hannah</td>\n",
              "      <td id=\"T_e2de1_row14_col3\" class=\"data row14 col3\" >37772146</td>\n",
              "      <td id=\"T_e2de1_row14_col4\" class=\"data row14 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
              "      <td id=\"T_e2de1_row15_col0\" class=\"data row15 col0\" >Central&Spacious 3Bed Apt at Zetland | MHS</td>\n",
              "      <td id=\"T_e2de1_row15_col1\" class=\"data row15 col1\" >672903848532202580</td>\n",
              "      <td id=\"T_e2de1_row15_col2\" class=\"data row15 col2\" >Evan</td>\n",
              "      <td id=\"T_e2de1_row15_col3\" class=\"data row15 col3\" >185126628</td>\n",
              "      <td id=\"T_e2de1_row15_col4\" class=\"data row15 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
              "      <td id=\"T_e2de1_row16_col0\" class=\"data row16 col0\" >Bayside Studio close to City with amazing view</td>\n",
              "      <td id=\"T_e2de1_row16_col1\" class=\"data row16 col1\" >22234562</td>\n",
              "      <td id=\"T_e2de1_row16_col2\" class=\"data row16 col2\" >Jackie</td>\n",
              "      <td id=\"T_e2de1_row16_col3\" class=\"data row16 col3\" >43502287</td>\n",
              "      <td id=\"T_e2de1_row16_col4\" class=\"data row16 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
              "      <td id=\"T_e2de1_row17_col0\" class=\"data row17 col0\" >Centrally located, spacious, cute, sunny studio!</td>\n",
              "      <td id=\"T_e2de1_row17_col1\" class=\"data row17 col1\" >16411678</td>\n",
              "      <td id=\"T_e2de1_row17_col2\" class=\"data row17 col2\" >Alzbeta</td>\n",
              "      <td id=\"T_e2de1_row17_col3\" class=\"data row17 col3\" >1043937</td>\n",
              "      <td id=\"T_e2de1_row17_col4\" class=\"data row17 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
              "      <td id=\"T_e2de1_row18_col0\" class=\"data row18 col0\" >悉尼情人港、唐人街旁新房公寓，近一切设施</td>\n",
              "      <td id=\"T_e2de1_row18_col1\" class=\"data row18 col1\" >41222429</td>\n",
              "      <td id=\"T_e2de1_row18_col2\" class=\"data row18 col2\" >Shu</td>\n",
              "      <td id=\"T_e2de1_row18_col3\" class=\"data row18 col3\" >120290790</td>\n",
              "      <td id=\"T_e2de1_row18_col4\" class=\"data row18 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
              "      <td id=\"T_e2de1_row19_col0\" class=\"data row19 col0\" >Double Room w. Shared Bathroom</td>\n",
              "      <td id=\"T_e2de1_row19_col1\" class=\"data row19 col1\" >33757329</td>\n",
              "      <td id=\"T_e2de1_row19_col2\" class=\"data row19 col2\" >The Jensen Potts Point</td>\n",
              "      <td id=\"T_e2de1_row19_col3\" class=\"data row19 col3\" >254427140</td>\n",
              "      <td id=\"T_e2de1_row19_col4\" class=\"data row19 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row20\" class=\"row_heading level0 row20\" >20</th>\n",
              "      <td id=\"T_e2de1_row20_col0\" class=\"data row20 col0\" >MARVELOUS LOCATION cool city studio, AC & balcony</td>\n",
              "      <td id=\"T_e2de1_row20_col1\" class=\"data row20 col1\" >45917438</td>\n",
              "      <td id=\"T_e2de1_row20_col2\" class=\"data row20 col2\" >Peter & Stephanie</td>\n",
              "      <td id=\"T_e2de1_row20_col3\" class=\"data row20 col3\" >215158027</td>\n",
              "      <td id=\"T_e2de1_row20_col4\" class=\"data row20 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row21\" class=\"row_heading level0 row21\" >21</th>\n",
              "      <td id=\"T_e2de1_row21_col0\" class=\"data row21 col0\" >Urban Oasis in Sydney CBD!</td>\n",
              "      <td id=\"T_e2de1_row21_col1\" class=\"data row21 col1\" >9354832</td>\n",
              "      <td id=\"T_e2de1_row21_col2\" class=\"data row21 col2\" >Alaura</td>\n",
              "      <td id=\"T_e2de1_row21_col3\" class=\"data row21 col3\" >40724867</td>\n",
              "      <td id=\"T_e2de1_row21_col4\" class=\"data row21 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row22\" class=\"row_heading level0 row22\" >22</th>\n",
              "      <td id=\"T_e2de1_row22_col0\" class=\"data row22 col0\" >Leafy Sydney City Apartment</td>\n",
              "      <td id=\"T_e2de1_row22_col1\" class=\"data row22 col1\" >4632356</td>\n",
              "      <td id=\"T_e2de1_row22_col2\" class=\"data row22 col2\" >Monica</td>\n",
              "      <td id=\"T_e2de1_row22_col3\" class=\"data row22 col3\" >23988683</td>\n",
              "      <td id=\"T_e2de1_row22_col4\" class=\"data row22 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row23\" class=\"row_heading level0 row23\" >23</th>\n",
              "      <td id=\"T_e2de1_row23_col0\" class=\"data row23 col0\" >Balgowlah Beach House</td>\n",
              "      <td id=\"T_e2de1_row23_col1\" class=\"data row23 col1\" >9012529</td>\n",
              "      <td id=\"T_e2de1_row23_col2\" class=\"data row23 col2\" >Marnie</td>\n",
              "      <td id=\"T_e2de1_row23_col3\" class=\"data row23 col3\" >22227415</td>\n",
              "      <td id=\"T_e2de1_row23_col4\" class=\"data row23 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row24\" class=\"row_heading level0 row24\" >24</th>\n",
              "      <td id=\"T_e2de1_row24_col0\" class=\"data row24 col0\" >Perfect beachside getaway footsteps to the beach.</td>\n",
              "      <td id=\"T_e2de1_row24_col1\" class=\"data row24 col1\" >21443493</td>\n",
              "      <td id=\"T_e2de1_row24_col2\" class=\"data row24 col2\" >Mel</td>\n",
              "      <td id=\"T_e2de1_row24_col3\" class=\"data row24 col3\" >88281040</td>\n",
              "      <td id=\"T_e2de1_row24_col4\" class=\"data row24 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row25\" class=\"row_heading level0 row25\" >25</th>\n",
              "      <td id=\"T_e2de1_row25_col0\" class=\"data row25 col0\" >Manly Beach House</td>\n",
              "      <td id=\"T_e2de1_row25_col1\" class=\"data row25 col1\" >10729688</td>\n",
              "      <td id=\"T_e2de1_row25_col2\" class=\"data row25 col2\" >Rebecca</td>\n",
              "      <td id=\"T_e2de1_row25_col3\" class=\"data row25 col3\" >13657320</td>\n",
              "      <td id=\"T_e2de1_row25_col4\" class=\"data row25 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row26\" class=\"row_heading level0 row26\" >26</th>\n",
              "      <td id=\"T_e2de1_row26_col0\" class=\"data row26 col0\" >Stylish Garden Apartment near Manly beach</td>\n",
              "      <td id=\"T_e2de1_row26_col1\" class=\"data row26 col1\" >20628052</td>\n",
              "      <td id=\"T_e2de1_row26_col2\" class=\"data row26 col2\" >Claire</td>\n",
              "      <td id=\"T_e2de1_row26_col3\" class=\"data row26 col3\" >3444681</td>\n",
              "      <td id=\"T_e2de1_row26_col4\" class=\"data row26 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row27\" class=\"row_heading level0 row27\" >27</th>\n",
              "      <td id=\"T_e2de1_row27_col0\" class=\"data row27 col0\" >Sun-filled 2 bedroom unit, a stroll to the beach</td>\n",
              "      <td id=\"T_e2de1_row27_col1\" class=\"data row27 col1\" >54362721</td>\n",
              "      <td id=\"T_e2de1_row27_col2\" class=\"data row27 col2\" >Barry</td>\n",
              "      <td id=\"T_e2de1_row27_col3\" class=\"data row27 col3\" >51257703</td>\n",
              "      <td id=\"T_e2de1_row27_col4\" class=\"data row27 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row28\" class=\"row_heading level0 row28\" >28</th>\n",
              "      <td id=\"T_e2de1_row28_col0\" class=\"data row28 col0\" >Space is everything</td>\n",
              "      <td id=\"T_e2de1_row28_col1\" class=\"data row28 col1\" >52704433</td>\n",
              "      <td id=\"T_e2de1_row28_col2\" class=\"data row28 col2\" >Hudson</td>\n",
              "      <td id=\"T_e2de1_row28_col3\" class=\"data row28 col3\" >54924558</td>\n",
              "      <td id=\"T_e2de1_row28_col4\" class=\"data row28 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row29\" class=\"row_heading level0 row29\" >29</th>\n",
              "      <td id=\"T_e2de1_row29_col0\" class=\"data row29 col0\" >Convenient 2 bedroom Rhodes apartment</td>\n",
              "      <td id=\"T_e2de1_row29_col1\" class=\"data row29 col1\" >706030270420864343</td>\n",
              "      <td id=\"T_e2de1_row29_col2\" class=\"data row29 col2\" >Bin</td>\n",
              "      <td id=\"T_e2de1_row29_col3\" class=\"data row29 col3\" >465696336</td>\n",
              "      <td id=\"T_e2de1_row29_col4\" class=\"data row29 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row30\" class=\"row_heading level0 row30\" >30</th>\n",
              "      <td id=\"T_e2de1_row30_col0\" class=\"data row30 col0\" >3 Bedroom Apartment with Kitchen & Balcony</td>\n",
              "      <td id=\"T_e2de1_row30_col1\" class=\"data row30 col1\" >18999835</td>\n",
              "      <td id=\"T_e2de1_row30_col2\" class=\"data row30 col2\" >Twin Towers Inn</td>\n",
              "      <td id=\"T_e2de1_row30_col3\" class=\"data row30 col3\" >108189888</td>\n",
              "      <td id=\"T_e2de1_row30_col4\" class=\"data row30 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row31\" class=\"row_heading level0 row31\" >31</th>\n",
              "      <td id=\"T_e2de1_row31_col0\" class=\"data row31 col0\" >Clifton Road Clovelly</td>\n",
              "      <td id=\"T_e2de1_row31_col1\" class=\"data row31 col1\" >10103846</td>\n",
              "      <td id=\"T_e2de1_row31_col2\" class=\"data row31 col2\" >Craig</td>\n",
              "      <td id=\"T_e2de1_row31_col3\" class=\"data row31 col3\" >51850460</td>\n",
              "      <td id=\"T_e2de1_row31_col4\" class=\"data row31 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row32\" class=\"row_heading level0 row32\" >32</th>\n",
              "      <td id=\"T_e2de1_row32_col0\" class=\"data row32 col0\" >Modern Randwick apartment</td>\n",
              "      <td id=\"T_e2de1_row32_col1\" class=\"data row32 col1\" >7846383</td>\n",
              "      <td id=\"T_e2de1_row32_col2\" class=\"data row32 col2\" >Marcela</td>\n",
              "      <td id=\"T_e2de1_row32_col3\" class=\"data row32 col3\" >40070341</td>\n",
              "      <td id=\"T_e2de1_row32_col4\" class=\"data row32 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row33\" class=\"row_heading level0 row33\" >33</th>\n",
              "      <td id=\"T_e2de1_row33_col0\" class=\"data row33 col0\" >Big room close to CBD with backyard & BBQ</td>\n",
              "      <td id=\"T_e2de1_row33_col1\" class=\"data row33 col1\" >19164401</td>\n",
              "      <td id=\"T_e2de1_row33_col2\" class=\"data row33 col2\" >Malin</td>\n",
              "      <td id=\"T_e2de1_row33_col3\" class=\"data row33 col3\" >61907880</td>\n",
              "      <td id=\"T_e2de1_row33_col4\" class=\"data row33 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row34\" class=\"row_heading level0 row34\" >34</th>\n",
              "      <td id=\"T_e2de1_row34_col0\" class=\"data row34 col0\" >Beautiful 2 bedroom unit - Kensington</td>\n",
              "      <td id=\"T_e2de1_row34_col1\" class=\"data row34 col1\" >30801418</td>\n",
              "      <td id=\"T_e2de1_row34_col2\" class=\"data row34 col2\" >Nathalia</td>\n",
              "      <td id=\"T_e2de1_row34_col3\" class=\"data row34 col3\" >16006077</td>\n",
              "      <td id=\"T_e2de1_row34_col4\" class=\"data row34 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row35\" class=\"row_heading level0 row35\" >35</th>\n",
              "      <td id=\"T_e2de1_row35_col0\" class=\"data row35 col0\" >Convenient home with high end finish at MQ Park</td>\n",
              "      <td id=\"T_e2de1_row35_col1\" class=\"data row35 col1\" >582106097096200272</td>\n",
              "      <td id=\"T_e2de1_row35_col2\" class=\"data row35 col2\" >AirPillows</td>\n",
              "      <td id=\"T_e2de1_row35_col3\" class=\"data row35 col3\" >301753450</td>\n",
              "      <td id=\"T_e2de1_row35_col4\" class=\"data row35 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row36\" class=\"row_heading level0 row36\" >36</th>\n",
              "      <td id=\"T_e2de1_row36_col0\" class=\"data row36 col0\" >In the Middle of Sydney with Beautiful River View</td>\n",
              "      <td id=\"T_e2de1_row36_col1\" class=\"data row36 col1\" >50118577</td>\n",
              "      <td id=\"T_e2de1_row36_col2\" class=\"data row36 col2\" >Yihan</td>\n",
              "      <td id=\"T_e2de1_row36_col3\" class=\"data row36 col3\" >169795871</td>\n",
              "      <td id=\"T_e2de1_row36_col4\" class=\"data row36 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row37\" class=\"row_heading level0 row37\" >37</th>\n",
              "      <td id=\"T_e2de1_row37_col0\" class=\"data row37 col0\" >Skyline 2Bedroom+1Study room Apt&Free park&Pet ok</td>\n",
              "      <td id=\"T_e2de1_row37_col1\" class=\"data row37 col1\" >697876946371921032</td>\n",
              "      <td id=\"T_e2de1_row37_col2\" class=\"data row37 col2\" >Zeam</td>\n",
              "      <td id=\"T_e2de1_row37_col3\" class=\"data row37 col3\" >457666854</td>\n",
              "      <td id=\"T_e2de1_row37_col4\" class=\"data row37 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row38\" class=\"row_heading level0 row38\" >38</th>\n",
              "      <td id=\"T_e2de1_row38_col0\" class=\"data row38 col0\" >Tranquil place in great location</td>\n",
              "      <td id=\"T_e2de1_row38_col1\" class=\"data row38 col1\" >46434467</td>\n",
              "      <td id=\"T_e2de1_row38_col2\" class=\"data row38 col2\" >Anna</td>\n",
              "      <td id=\"T_e2de1_row38_col3\" class=\"data row38 col3\" >1739066</td>\n",
              "      <td id=\"T_e2de1_row38_col4\" class=\"data row38 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row39\" class=\"row_heading level0 row39\" >39</th>\n",
              "      <td id=\"T_e2de1_row39_col0\" class=\"data row39 col0\" >Grand, historic terrace on park</td>\n",
              "      <td id=\"T_e2de1_row39_col1\" class=\"data row39 col1\" >7581665</td>\n",
              "      <td id=\"T_e2de1_row39_col2\" class=\"data row39 col2\" >Maggie</td>\n",
              "      <td id=\"T_e2de1_row39_col3\" class=\"data row39 col3\" >2847618</td>\n",
              "      <td id=\"T_e2de1_row39_col4\" class=\"data row39 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row40\" class=\"row_heading level0 row40\" >40</th>\n",
              "      <td id=\"T_e2de1_row40_col0\" class=\"data row40 col0\" >Luxurious Cozy Bedroom, Explore @Olympic Park</td>\n",
              "      <td id=\"T_e2de1_row40_col1\" class=\"data row40 col1\" >34027782</td>\n",
              "      <td id=\"T_e2de1_row40_col2\" class=\"data row40 col2\" >Zach</td>\n",
              "      <td id=\"T_e2de1_row40_col3\" class=\"data row40 col3\" >163419570</td>\n",
              "      <td id=\"T_e2de1_row40_col4\" class=\"data row40 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row41\" class=\"row_heading level0 row41\" >41</th>\n",
              "      <td id=\"T_e2de1_row41_col0\" class=\"data row41 col0\" >Lovely House in center of Eastwood</td>\n",
              "      <td id=\"T_e2de1_row41_col1\" class=\"data row41 col1\" >28414409</td>\n",
              "      <td id=\"T_e2de1_row41_col2\" class=\"data row41 col2\" >Shaoyong</td>\n",
              "      <td id=\"T_e2de1_row41_col3\" class=\"data row41 col3\" >213725582</td>\n",
              "      <td id=\"T_e2de1_row41_col4\" class=\"data row41 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row42\" class=\"row_heading level0 row42\" >42</th>\n",
              "      <td id=\"T_e2de1_row42_col0\" class=\"data row42 col0\" >Silver Beach Penthouse</td>\n",
              "      <td id=\"T_e2de1_row42_col1\" class=\"data row42 col1\" >644585184959945914</td>\n",
              "      <td id=\"T_e2de1_row42_col2\" class=\"data row42 col2\" >Victoria</td>\n",
              "      <td id=\"T_e2de1_row42_col3\" class=\"data row42 col3\" >440651046</td>\n",
              "      <td id=\"T_e2de1_row42_col4\" class=\"data row42 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row43\" class=\"row_heading level0 row43\" >43</th>\n",
              "      <td id=\"T_e2de1_row43_col0\" class=\"data row43 col0\" >Silverbeach Tourist Park Kurnell</td>\n",
              "      <td id=\"T_e2de1_row43_col1\" class=\"data row43 col1\" >11596728</td>\n",
              "      <td id=\"T_e2de1_row43_col2\" class=\"data row43 col2\" >Rebecca</td>\n",
              "      <td id=\"T_e2de1_row43_col3\" class=\"data row43 col3\" >61355054</td>\n",
              "      <td id=\"T_e2de1_row43_col4\" class=\"data row43 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row44\" class=\"row_heading level0 row44\" >44</th>\n",
              "      <td id=\"T_e2de1_row44_col0\" class=\"data row44 col0\" >Cozy double room @ King St, Newtown</td>\n",
              "      <td id=\"T_e2de1_row44_col1\" class=\"data row44 col1\" >8476652</td>\n",
              "      <td id=\"T_e2de1_row44_col2\" class=\"data row44 col2\" >Austin</td>\n",
              "      <td id=\"T_e2de1_row44_col3\" class=\"data row44 col3\" >38507923</td>\n",
              "      <td id=\"T_e2de1_row44_col4\" class=\"data row44 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row45\" class=\"row_heading level0 row45\" >45</th>\n",
              "      <td id=\"T_e2de1_row45_col0\" class=\"data row45 col0\" >Large private room on Camperdown park & Newtown</td>\n",
              "      <td id=\"T_e2de1_row45_col1\" class=\"data row45 col1\" >22296011</td>\n",
              "      <td id=\"T_e2de1_row45_col2\" class=\"data row45 col2\" >Joshua</td>\n",
              "      <td id=\"T_e2de1_row45_col3\" class=\"data row45 col3\" >10873080</td>\n",
              "      <td id=\"T_e2de1_row45_col4\" class=\"data row45 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row46\" class=\"row_heading level0 row46\" >46</th>\n",
              "      <td id=\"T_e2de1_row46_col0\" class=\"data row46 col0\" >Spacious 1-bedroom guest house with self check in!</td>\n",
              "      <td id=\"T_e2de1_row46_col1\" class=\"data row46 col1\" >669376581695271090</td>\n",
              "      <td id=\"T_e2de1_row46_col2\" class=\"data row46 col2\" >Karen</td>\n",
              "      <td id=\"T_e2de1_row46_col3\" class=\"data row46 col3\" >16803308</td>\n",
              "      <td id=\"T_e2de1_row46_col4\" class=\"data row46 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row47\" class=\"row_heading level0 row47\" >47</th>\n",
              "      <td id=\"T_e2de1_row47_col0\" class=\"data row47 col0\" >Cozy, new 2-bedroom with parking</td>\n",
              "      <td id=\"T_e2de1_row47_col1\" class=\"data row47 col1\" >22311178</td>\n",
              "      <td id=\"T_e2de1_row47_col2\" class=\"data row47 col2\" >Ken</td>\n",
              "      <td id=\"T_e2de1_row47_col3\" class=\"data row47 col3\" >163113803</td>\n",
              "      <td id=\"T_e2de1_row47_col4\" class=\"data row47 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row48\" class=\"row_heading level0 row48\" >48</th>\n",
              "      <td id=\"T_e2de1_row48_col0\" class=\"data row48 col0\" >Duffotel Paddington EXEC 2-Bedroom Apartment</td>\n",
              "      <td id=\"T_e2de1_row48_col1\" class=\"data row48 col1\" >41983356</td>\n",
              "      <td id=\"T_e2de1_row48_col2\" class=\"data row48 col2\" >Duffotel</td>\n",
              "      <td id=\"T_e2de1_row48_col3\" class=\"data row48 col3\" >234423891</td>\n",
              "      <td id=\"T_e2de1_row48_col4\" class=\"data row48 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row49\" class=\"row_heading level0 row49\" >49</th>\n",
              "      <td id=\"T_e2de1_row49_col0\" class=\"data row49 col0\" >******Paddington Sydney*****</td>\n",
              "      <td id=\"T_e2de1_row49_col1\" class=\"data row49 col1\" >10111788</td>\n",
              "      <td id=\"T_e2de1_row49_col2\" class=\"data row49 col2\" >Tom</td>\n",
              "      <td id=\"T_e2de1_row49_col3\" class=\"data row49 col3\" >51892300</td>\n",
              "      <td id=\"T_e2de1_row49_col4\" class=\"data row49 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row50\" class=\"row_heading level0 row50\" >50</th>\n",
              "      <td id=\"T_e2de1_row50_col0\" class=\"data row50 col0\" >Lovely bedroom by at the sea.</td>\n",
              "      <td id=\"T_e2de1_row50_col1\" class=\"data row50 col1\" >658279799851154919</td>\n",
              "      <td id=\"T_e2de1_row50_col2\" class=\"data row50 col2\" >Maurice</td>\n",
              "      <td id=\"T_e2de1_row50_col3\" class=\"data row50 col3\" >3526500</td>\n",
              "      <td id=\"T_e2de1_row50_col4\" class=\"data row50 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row51\" class=\"row_heading level0 row51\" >51</th>\n",
              "      <td id=\"T_e2de1_row51_col0\" class=\"data row51 col0\" >Amazing Double Bay Apartment with High Ceilings</td>\n",
              "      <td id=\"T_e2de1_row51_col1\" class=\"data row51 col1\" >10549608</td>\n",
              "      <td id=\"T_e2de1_row51_col2\" class=\"data row51 col2\" >Cyrus</td>\n",
              "      <td id=\"T_e2de1_row51_col3\" class=\"data row51 col3\" >23953750</td>\n",
              "      <td id=\"T_e2de1_row51_col4\" class=\"data row51 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row52\" class=\"row_heading level0 row52\" >52</th>\n",
              "      <td id=\"T_e2de1_row52_col0\" class=\"data row52 col0\" >Beautifully Renovated Paddington Apartment</td>\n",
              "      <td id=\"T_e2de1_row52_col1\" class=\"data row52 col1\" >2033151</td>\n",
              "      <td id=\"T_e2de1_row52_col2\" class=\"data row52 col2\" >Julia</td>\n",
              "      <td id=\"T_e2de1_row52_col3\" class=\"data row52 col3\" >5727462</td>\n",
              "      <td id=\"T_e2de1_row52_col4\" class=\"data row52 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row53\" class=\"row_heading level0 row53\" >53</th>\n",
              "      <td id=\"T_e2de1_row53_col0\" class=\"data row53 col0\" >Family Beach House🌴</td>\n",
              "      <td id=\"T_e2de1_row53_col1\" class=\"data row53 col1\" >9376988</td>\n",
              "      <td id=\"T_e2de1_row53_col2\" class=\"data row53 col2\" >Emma</td>\n",
              "      <td id=\"T_e2de1_row53_col3\" class=\"data row53 col3\" >11196275</td>\n",
              "      <td id=\"T_e2de1_row53_col4\" class=\"data row53 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row54\" class=\"row_heading level0 row54\" >54</th>\n",
              "      <td id=\"T_e2de1_row54_col0\" class=\"data row54 col0\" >BEACHCOMBER NORTH AVALON</td>\n",
              "      <td id=\"T_e2de1_row54_col1\" class=\"data row54 col1\" >44284905</td>\n",
              "      <td id=\"T_e2de1_row54_col2\" class=\"data row54 col2\" >Jill</td>\n",
              "      <td id=\"T_e2de1_row54_col3\" class=\"data row54 col3\" >269689583</td>\n",
              "      <td id=\"T_e2de1_row54_col4\" class=\"data row54 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row55\" class=\"row_heading level0 row55\" >55</th>\n",
              "      <td id=\"T_e2de1_row55_col0\" class=\"data row55 col0\" >Warriewood Beach House</td>\n",
              "      <td id=\"T_e2de1_row55_col1\" class=\"data row55 col1\" >46159786</td>\n",
              "      <td id=\"T_e2de1_row55_col2\" class=\"data row55 col2\" >Fiona</td>\n",
              "      <td id=\"T_e2de1_row55_col3\" class=\"data row55 col3\" >136836511</td>\n",
              "      <td id=\"T_e2de1_row55_col4\" class=\"data row55 col4\" >Expensive</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row56\" class=\"row_heading level0 row56\" >56</th>\n",
              "      <td id=\"T_e2de1_row56_col0\" class=\"data row56 col0\" >Relaxing Haberfield home on a large leafy property</td>\n",
              "      <td id=\"T_e2de1_row56_col1\" class=\"data row56 col1\" >37999560</td>\n",
              "      <td id=\"T_e2de1_row56_col2\" class=\"data row56 col2\" >Derek</td>\n",
              "      <td id=\"T_e2de1_row56_col3\" class=\"data row56 col3\" >71234146</td>\n",
              "      <td id=\"T_e2de1_row56_col4\" class=\"data row56 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row57\" class=\"row_heading level0 row57\" >57</th>\n",
              "      <td id=\"T_e2de1_row57_col0\" class=\"data row57 col0\" >Switch off and recharge in Lower Macdonald</td>\n",
              "      <td id=\"T_e2de1_row57_col1\" class=\"data row57 col1\" >657313869212267718</td>\n",
              "      <td id=\"T_e2de1_row57_col2\" class=\"data row57 col2\" >Emma</td>\n",
              "      <td id=\"T_e2de1_row57_col3\" class=\"data row57 col3\" >10438629</td>\n",
              "      <td id=\"T_e2de1_row57_col4\" class=\"data row57 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row58\" class=\"row_heading level0 row58\" >58</th>\n",
              "      <td id=\"T_e2de1_row58_col0\" class=\"data row58 col0\" >Amazing private room in Wolli Creek</td>\n",
              "      <td id=\"T_e2de1_row58_col1\" class=\"data row58 col1\" >28268415</td>\n",
              "      <td id=\"T_e2de1_row58_col2\" class=\"data row58 col2\" >Maíra</td>\n",
              "      <td id=\"T_e2de1_row58_col3\" class=\"data row58 col3\" >42427723</td>\n",
              "      <td id=\"T_e2de1_row58_col4\" class=\"data row58 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row59\" class=\"row_heading level0 row59\" >59</th>\n",
              "      <td id=\"T_e2de1_row59_col0\" class=\"data row59 col0\" >Excellent room in nice location</td>\n",
              "      <td id=\"T_e2de1_row59_col1\" class=\"data row59 col1\" >11126443</td>\n",
              "      <td id=\"T_e2de1_row59_col2\" class=\"data row59 col2\" >Steven</td>\n",
              "      <td id=\"T_e2de1_row59_col3\" class=\"data row59 col3\" >23796463</td>\n",
              "      <td id=\"T_e2de1_row59_col4\" class=\"data row59 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row60\" class=\"row_heading level0 row60\" >60</th>\n",
              "      <td id=\"T_e2de1_row60_col0\" class=\"data row60 col0\" >North Appartment near City</td>\n",
              "      <td id=\"T_e2de1_row60_col1\" class=\"data row60 col1\" >13629326</td>\n",
              "      <td id=\"T_e2de1_row60_col2\" class=\"data row60 col2\" >Adam</td>\n",
              "      <td id=\"T_e2de1_row60_col3\" class=\"data row60 col3\" >79000479</td>\n",
              "      <td id=\"T_e2de1_row60_col4\" class=\"data row60 col4\" >Affordable</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row61\" class=\"row_heading level0 row61\" >61</th>\n",
              "      <td id=\"T_e2de1_row61_col0\" class=\"data row61 col0\" >Beautiful, sunny apartment conveniently located</td>\n",
              "      <td id=\"T_e2de1_row61_col1\" class=\"data row61 col1\" >21999407</td>\n",
              "      <td id=\"T_e2de1_row61_col2\" class=\"data row61 col2\" >Karen</td>\n",
              "      <td id=\"T_e2de1_row61_col3\" class=\"data row61 col3\" >13931594</td>\n",
              "      <td id=\"T_e2de1_row61_col4\" class=\"data row61 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_e2de1_level0_row62\" class=\"row_heading level0 row62\" >62</th>\n",
              "      <td id=\"T_e2de1_row62_col0\" class=\"data row62 col0\" >Christmas rental short term</td>\n",
              "      <td id=\"T_e2de1_row62_col1\" class=\"data row62 col1\" >40426054</td>\n",
              "      <td id=\"T_e2de1_row62_col2\" class=\"data row62 col2\" >Brenda</td>\n",
              "      <td id=\"T_e2de1_row62_col3\" class=\"data row62 col3\" >78397109</td>\n",
              "      <td id=\"T_e2de1_row62_col4\" class=\"data row62 col4\" >Mid-range</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "---\n",
        "\n",
        "## (Extra Credit) Part 5: Represented or not?\n",
        "\n",
        "We're now interested in tracking all neighbourhoods in which we are \"over-represented\". Let's first count all the occurences of each neighbourhood in our `listings`-table. "
      ],
      "metadata": {
        "id": "JEkLwWA1Wa21"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_5_1a\n",
        "# Instructions: \n",
        "# 1/ Fetch neighbourhood_id from the listings table\n",
        "# 2/ For the second column get the number of listings in each neighbourhood\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT COUNT(neighbourhood_id), neighbourhood_id\n",
        "FROM listings\n",
        "GROUP BY neighbourhood_id\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_5_1a = query)"
      ],
      "metadata": {
        "id": "QsdAVWgzijZL",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 590
        },
        "outputId": "8fe52b0c-e6db-48a0-b69d-908ecb24692a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_3b3a3_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >COUNT(neighbourhood_id)</th>\n",
              "      <th class=\"col_heading level0 col1\" >neighbourhood_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_3b3a3_row0_col0\" class=\"data row0 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row0_col1\" class=\"data row0 col1\" >0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_3b3a3_row1_col0\" class=\"data row1 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row1_col1\" class=\"data row1 col1\" >1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_3b3a3_row2_col0\" class=\"data row2 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row2_col1\" class=\"data row2 col1\" >4</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_3b3a3_row3_col0\" class=\"data row3 col0\" >2</td>\n",
              "      <td id=\"T_3b3a3_row3_col1\" class=\"data row3 col1\" >8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row4\" class=\"row_heading level0 row4\" >4</th>\n",
              "      <td id=\"T_3b3a3_row4_col0\" class=\"data row4 col0\" >2</td>\n",
              "      <td id=\"T_3b3a3_row4_col1\" class=\"data row4 col1\" >11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row5\" class=\"row_heading level0 row5\" >5</th>\n",
              "      <td id=\"T_3b3a3_row5_col0\" class=\"data row5 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row5_col1\" class=\"data row5 col1\" >17</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row6\" class=\"row_heading level0 row6\" >6</th>\n",
              "      <td id=\"T_3b3a3_row6_col0\" class=\"data row6 col0\" >2</td>\n",
              "      <td id=\"T_3b3a3_row6_col1\" class=\"data row6 col1\" >18</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row7\" class=\"row_heading level0 row7\" >7</th>\n",
              "      <td id=\"T_3b3a3_row7_col0\" class=\"data row7 col0\" >5</td>\n",
              "      <td id=\"T_3b3a3_row7_col1\" class=\"data row7 col1\" >20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row8\" class=\"row_heading level0 row8\" >8</th>\n",
              "      <td id=\"T_3b3a3_row8_col0\" class=\"data row8 col0\" >2</td>\n",
              "      <td id=\"T_3b3a3_row8_col1\" class=\"data row8 col1\" >21</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row9\" class=\"row_heading level0 row9\" >9</th>\n",
              "      <td id=\"T_3b3a3_row9_col0\" class=\"data row9 col0\" >3</td>\n",
              "      <td id=\"T_3b3a3_row9_col1\" class=\"data row9 col1\" >23</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row10\" class=\"row_heading level0 row10\" >10</th>\n",
              "      <td id=\"T_3b3a3_row10_col0\" class=\"data row10 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row10_col1\" class=\"data row10 col1\" >24</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row11\" class=\"row_heading level0 row11\" >11</th>\n",
              "      <td id=\"T_3b3a3_row11_col0\" class=\"data row11 col0\" >3</td>\n",
              "      <td id=\"T_3b3a3_row11_col1\" class=\"data row11 col1\" >26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row12\" class=\"row_heading level0 row12\" >12</th>\n",
              "      <td id=\"T_3b3a3_row12_col0\" class=\"data row12 col0\" >4</td>\n",
              "      <td id=\"T_3b3a3_row12_col1\" class=\"data row12 col1\" >27</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row13\" class=\"row_heading level0 row13\" >13</th>\n",
              "      <td id=\"T_3b3a3_row13_col0\" class=\"data row13 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row13_col1\" class=\"data row13 col1\" >28</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row14\" class=\"row_heading level0 row14\" >14</th>\n",
              "      <td id=\"T_3b3a3_row14_col0\" class=\"data row14 col0\" >3</td>\n",
              "      <td id=\"T_3b3a3_row14_col1\" class=\"data row14 col1\" >29</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row15\" class=\"row_heading level0 row15\" >15</th>\n",
              "      <td id=\"T_3b3a3_row15_col0\" class=\"data row15 col0\" >2</td>\n",
              "      <td id=\"T_3b3a3_row15_col1\" class=\"data row15 col1\" >31</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row16\" class=\"row_heading level0 row16\" >16</th>\n",
              "      <td id=\"T_3b3a3_row16_col0\" class=\"data row16 col0\" >10</td>\n",
              "      <td id=\"T_3b3a3_row16_col1\" class=\"data row16 col1\" >32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row17\" class=\"row_heading level0 row17\" >17</th>\n",
              "      <td id=\"T_3b3a3_row17_col0\" class=\"data row17 col0\" >1</td>\n",
              "      <td id=\"T_3b3a3_row17_col1\" class=\"data row17 col1\" >33</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row18\" class=\"row_heading level0 row18\" >18</th>\n",
              "      <td id=\"T_3b3a3_row18_col0\" class=\"data row18 col0\" >13</td>\n",
              "      <td id=\"T_3b3a3_row18_col1\" class=\"data row18 col1\" >35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_3b3a3_level0_row19\" class=\"row_heading level0 row19\" >19</th>\n",
              "      <td id=\"T_3b3a3_row19_col0\" class=\"data row19 col0\" >5</td>\n",
              "      <td id=\"T_3b3a3_row19_col1\" class=\"data row19 col1\" >37</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's limit the results to only neighbourhoods that have a count higher than 4. (TIP: GROUP BY can be extended by making use of keyword HAVING)."
      ],
      "metadata": {
        "id": "7FvPNXiLisjo"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "---\n",
        "\n",
        "## 🎉 CONGRATULATIONS! \n",
        "\n",
        "You've made it to the end of the week 2 assignment! Awesome! Next step submit the project on the CoRise platform by filling the form at the bottom here: https://corise.com/course/sql-crash-course/module/week-2-project\n",
        "\n",
        "If you have any lingering questions, post them on Slack! As you know, we're always here to help.\n",
        "\n",
        "And, if you want any additional challenge questions, check out the bonus extensions below.\n",
        "\n",
        "### Bonus: Extensions\n",
        "- Can you think of improving the readability of SQL queries this weeks by using [AS](https://www.w3schools.com/sql/sql_ref_as.asp)?\n",
        "- Explore the data more and come up with other questions that you would like answered using SQL, now that you know more SQL concepts!"
      ],
      "metadata": {
        "id": "_buhQE6pEcxc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "### Question: q2_5_1b\n",
        "# Instructions: \n",
        "# 1/ Fetch neighbourhood_id from the listings table\n",
        "# 2/ For the second column get the number of listings in each neighbourhood\n",
        "# 3/ Filter this summarized count > 4 (greater than).\n",
        "\n",
        "# TO BE COMPLETED\n",
        "query = \"\"\"\n",
        "SELECT COUNT(neighbourhood_id), neighbourhood_id\n",
        "FROM listings\n",
        "GROUP BY neighbourhood_id\n",
        "HAVING COUNT(*) >4\n",
        "\n",
        "\n",
        "\"\"\"\n",
        "\n",
        "run(query)\n",
        "check(q2_5_1b = query)"
      ],
      "metadata": {
        "id": "L5m49_0TWZ6c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 212
        },
        "outputId": "22e68fe3-4af3-48da-8515-7a61d246e6df"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ],
            "text/html": [
              "\n",
              "<style>\n",
              "  html {\n",
              "    --td-font-color: black;\n",
              "    --font-color: black;\n",
              "    --background-color: #e0e0e0;\n",
              "  }\n",
              "  html[theme=dark] {\n",
              "    --td-font-color: white;\n",
              "    --font-color: black;\n",
              "    --background-color: #6688ff;\n",
              "  }\n",
              "  th {\n",
              "    background: #fbd44c;\n",
              "    color: var(--font-color);\n",
              "    font-size: 16px;\n",
              "    text-align: center;\n",
              "    font-weight: bold;\n",
              "  }\n",
              "  tr:nth-child(even) {\n",
              "    background-color: var(--background-color);\n",
              "    color: var(--font-color);\n",
              "  }\n",
              "  td {\n",
              "    font-size: 14px;\n",
              "    color: var(--td-font-color);\n",
              "  }\n",
              "</style>\n",
              "<div style='max-height: 500px; overflow: auto; width: fit-content; border-style: solid; border-width: 1px; border-color: #0139fe; font-family: GT Planar,Inter,Arial,sans-serif;'><style type=\"text/css\">\n",
              "</style>\n",
              "<table id=\"T_5bd76_\">\n",
              "  <thead>\n",
              "    <tr>\n",
              "      <th class=\"blank level0\" >&nbsp;</th>\n",
              "      <th class=\"col_heading level0 col0\" >COUNT(neighbourhood_id)</th>\n",
              "      <th class=\"col_heading level0 col1\" >neighbourhood_id</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th id=\"T_5bd76_level0_row0\" class=\"row_heading level0 row0\" >0</th>\n",
              "      <td id=\"T_5bd76_row0_col0\" class=\"data row0 col0\" >5</td>\n",
              "      <td id=\"T_5bd76_row0_col1\" class=\"data row0 col1\" >20</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_5bd76_level0_row1\" class=\"row_heading level0 row1\" >1</th>\n",
              "      <td id=\"T_5bd76_row1_col0\" class=\"data row1 col0\" >10</td>\n",
              "      <td id=\"T_5bd76_row1_col1\" class=\"data row1 col1\" >32</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_5bd76_level0_row2\" class=\"row_heading level0 row2\" >2</th>\n",
              "      <td id=\"T_5bd76_row2_col0\" class=\"data row2 col0\" >13</td>\n",
              "      <td id=\"T_5bd76_row2_col1\" class=\"data row2 col1\" >35</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th id=\"T_5bd76_level0_row3\" class=\"row_heading level0 row3\" >3</th>\n",
              "      <td id=\"T_5bd76_row3_col0\" class=\"data row3 col0\" >5</td>\n",
              "      <td id=\"T_5bd76_row3_col1\" class=\"data row3 col1\" >37</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ]
          },
          "metadata": {}
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "-------------------\n",
            "Your SQL query is correct!\n",
            "\n",
            "-------------------\n"
          ]
        }
      ]
    }
  ]
}