1.2.6_Airbnb_SQL_Questions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What's the most expensive listing? What else can you tell me about the listing?\n",
    "~~~~\n",
    "SELECT \n",
    "\tname, \n",
    "\tneighbourhood, \n",
    "\tprice \n",
    "FROM \n",
    "\tsfo_listings \n",
    "ORDER BY \n",
    "\tprice DESC \n",
    "LIMIT 5;\n",
    "~~~~\n",
    "Output: Columns are name, neighborhood and price.\n",
    "~~~~\n",
    "\"Test apartment\"\t\"Russian Hill\"\t\"9999\"\n",
    "\"3 bed & 2 bath in Diamond Heights with Bay view\"\t\"Diamond Heights\"\t\"9270\"\n",
    "\"NobHill Penthouse-Top View&Location\"\t\"Nob Hill\"\t\"9000\"\n",
    "\"Bright private room in the Parkside.\"\t\"Parkside\"\t\"8000\"\n",
    "\"Three Bridges Penthouse by ONEFINESTAY\"\t\"Pacific Heights\"\t\"8000\"\n",
    "~~~~\n",
    "This is a 'test' apartment and probably not real; Allison is the named host; it's in the Russian Hill neighbourhood and has zero reviews."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### What neighborhoods seem to be the most popular?\n",
    "~~~~\n",
    "SELECT \n",
    "\tneighbourhood,\n",
    "\tsum(number_of_reviews) as tot_reviews_by_neighborhood,\n",
    "\tsum(reviews_per_month) as rev_per_month_by_neighborhood\n",
    "FROM \n",
    "\tsfo_listings\n",
    "GROUP BY \n",
    "\tneighbourhood\n",
    "ORDER BY \n",
    "\ttot_reviews_by_neighborhood DESC\n",
    "--\trev_per_month_by_neighborhood desc;\n",
    "LIMIT 3;\n",
    "\n",
    "Output:\n",
    "\"Mission\"\t\"34412\"\t\"1066.21\"\n",
    "\"Western Addition\"\t\"23098\"\t\"719.12\"\n",
    "\"Bernal Heights\"\t\"20635\"\t\"697.76\"\n",
    "~~~~\n",
    "The above 3 neighborhoods have the most total reviews, so they seem to be the most popular.  The Mission neighborhood also has the higest number of reviews per month on average.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  What time of year is the cheapest time to go to your city? What about the busiest?\n",
    "~~~~\n",
    "\n",
    "-- extract the month from the calender_date in order to group by month later.\n",
    "WITH\n",
    "    get_month\n",
    "AS (\n",
    "\tSELECT \n",
    "\t\tlisting_id,\n",
    "\t\tprice,\n",
    "\t\tEXTRACT (month from calender_date),\n",
    "\t\tREPLACE(price, ',', '') AS new_price\n",
    "\tFROM \n",
    " \t\tsfo_calender\n",
    "\tWHERE \n",
    " \t\tavailable = 't'\n",
    "),\n",
    "\n",
    "-- create new_table and float_table to change the price from text to float\n",
    "new_table AS (\n",
    "SELECT \n",
    "\tget_month.date_part,\n",
    "\tREPLACE(get_month.new_price, '$', '') AS new_price2\n",
    "FROM \n",
    " \tget_month\n",
    "),\n",
    "float_table AS (\n",
    "\tSELECT \n",
    "\t\tdate_part,\n",
    "\t\tCAST (new_price2 AS float)\n",
    "\tFROM\n",
    "\t\tnew_table\n",
    ")\n",
    "\n",
    "-- find the avg, min and max prices for listings which have availability as determined by the sfo_calender table.\n",
    "SELECT\n",
    "\tdate_part,\n",
    "\tAVG(new_price2) AS average_price,\n",
    "\tMIN(new_price2) AS min_price,\n",
    "\tMAX(new_price2) AS max_price\n",
    "FROM \n",
    "\tfloat_table\n",
    "GROUP BY \n",
    "\tdate_part\n",
    "ORDER BY\n",
    "\taverage_price;\n",
    "~~~~\n",
    "\n",
    "Output: Columns are month, avg price, min price and max price.\n",
    "~~~~\n",
    "\"4\"\t\"214.287218577944\"\t\"10\"\t\"22625\"\n",
    "\"3\"\t\"218.815850325836\"\t\"10\"\t\"220713\"\n",
    "\"5\"\t\"220.897306516322\"\t\"10\"\t\"23694\"\n",
    "\"11\"\t\"222.648999419954\"\t\"28\"\t\"24222\"\n",
    "\"10\"\t\"224.875831302894\"\t\"28\"\t\"24222\"\n",
    "\"1\"\t\"226.754231427219\"\t\"10\"\t\"162032\"\n",
    "\"6\"\t\"227.576895619507\"\t\"10\"\t\"24222\"\n",
    "\"9\"\t\"229.409688083243\"\t\"28\"\t\"24222\"\n",
    "\"12\"\t\"232.323379796287\"\t\"10\"\t\"135985\"\n",
    "\"8\"\t\"232.504816165468\"\t\"10\"\t\"24222\"\n",
    "\"7\"\t\"232.591136444277\"\t\"10\"\t\"24222\"\n",
    "\"2\"\t\"235.360793972514\"\t\"10\"\t\"220713\"\n",
    "~~~~\n",
    "\n",
    "Answer:  Months of March, April and May are the cheapest months by average, so Spring is the cheapest time to go to San Francisco.  The busiest time of year is presumably at the time where prices are most expensive, so Feb, July and August seem to be the busiest.  It looks like there might be so erroneous data, given there are max values of 220,713.00 for the month of February and March; January also has a max value of 162,032.00.  These high values could be due to the fact that they span many days or weeks or months, so more investigation is needed.  \n",
    "\n",
    "A better way to evaluate the prices per listing id would be to analyze the history of prices obtained per listing id, so we can see that prices where the renatals actually occurred. This information was not available as part of this exercise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}