1.2.5 - SQL Joins and CTE's.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 1.  What are the three longest trips on rainy days?\n",
    "~~~~\n",
    "SELECT \n",
    "\ttrip_id,\n",
    "\tevents,\n",
    "\tduration \n",
    "FROM \n",
    "\tweather \n",
    "JOIN \n",
    "\ttrips \n",
    "ON \n",
    "\tweather.zip=trips.zip_code\n",
    "WHERE \n",
    "\tweather.events='Rain' \n",
    "GROUP BY \n",
    "\ttrip_id,\n",
    "\tevents,\n",
    "\tduration\n",
    "ORDER BY \n",
    "\tduration desc\n",
    "LIMIT 3;\n",
    "~~~~\n",
    "Output: \n",
    "-- Columns are 'trip_id', 'events' and 'duration'\n",
    "~~~~\n",
    "\"1210487\"\t\"Rain\"\t\"83915\"\n",
    "\"1244903\"\t\"Rain\"\t\"81687\"\n",
    "\"1011634\"\t\"Rain\"\t\"81484\"\n",
    "~~~~\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.  Which station is full most often?\n",
    "~~~~\n",
    "SELECT\n",
    "\tstation_id,\n",
    "\tcount(*)\n",
    "FROM \n",
    "\tstatus\n",
    "WHERE\n",
    "\tdocks_available=0\n",
    "GROUP BY\n",
    "\tstation_id\n",
    "ORDER BY\n",
    "\tcount desc\n",
    "LIMIT 1000;\n",
    "~~~~\n",
    "Output - columns are 'station_id' and 'count':\n",
    "~~~~\n",
    "4\t\"5788\"\n",
    "3\t\"1444\"\n",
    "2\t\"626\"\n",
    "~~~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.  Return a list of stations with a count of number of trips starting at that station but ordered by dock count.\n",
    "~~~~\n",
    "WITH\n",
    "\ttrips_start\n",
    "AS (\n",
    "\tSELECT \n",
    "\t\t* \n",
    "\tFROM\n",
    "\t\tstations\n",
    "\tJOIN\n",
    "\t\ttrips\n",
    "\tON\n",
    "\t\tstations.name=trips.start_station\n",
    ")\n",
    "SELECT \n",
    "\tNAME,\n",
    "\ttrips_start.dockcount,\n",
    "\tcount(*)\n",
    "FROM\n",
    "\ttrips_start\n",
    "GROUP BY \n",
    "\tname,\n",
    "\ttrips_start.dockcount\n",
    "ORDER BY \n",
    "\ttrips_start.dockcount\n",
    "LIMIT 10;\n",
    "\n",
    "~~~~\n",
    "Output - columns are 'name' for station name, 'dockcount' and 'count' for number of trips started at that station:\n",
    "~~~~\n",
    "\"Santa Clara at Almaden\"\t11\t\"1447\"\n",
    "\"Cowper at University\"\t11\t\"788\"\n",
    "\"University and Emerson\"\t11\t\"490\"\n",
    "\"Castro Street and El Camino Real\"\t11\t\"1014\"\n",
    "\"Commercial at Montgomery\"\t15\t\"4969\"\n",
    "\"San Jose City Hall\"\t15\t\"518\"\n",
    "\"Mountain View City Hall\"\t15\t\"1291\"\n",
    "\"Broadway St at Battery St\"\t15\t\"5695\"\n",
    "\"St James Park\"\t15\t\"694\"\n",
    "\"Santa Clara County Civic Center\"\t15\t\"420\"\n",
    "~~~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.  (Challenge) What's the length of the longest trip for each day it rains anywhere?\n",
    "~~~~"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}