advanced_sql/02-analytic-functions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Advanced SQL Home Page](https://www.kaggle.com/learn/advanced-sql)**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "Here, you'll use window functions to answer questions about the [Chicago Taxi Trips](https://www.kaggle.com/chicago/chicago-taxi-trips-bq) dataset.\n",
    "\n",
    "Before you get started, run the code cell below to set everything up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n",
      "Setup Complete\n"
     ]
    }
   ],
   "source": [
    "# Set up feedback system\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.sql_advanced.ex2 import *\n",
    "print(\"Setup Complete\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The following code cell fetches the `taxi_trips` table from the `chicago_taxi_trips` dataset. We also preview the first five rows of the table.  You'll use the table to answer the questions below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>unique_key</th>\n",
       "      <th>taxi_id</th>\n",
       "      <th>trip_start_timestamp</th>\n",
       "      <th>trip_end_timestamp</th>\n",
       "      <th>trip_seconds</th>\n",
       "      <th>trip_miles</th>\n",
       "      <th>pickup_census_tract</th>\n",
       "      <th>dropoff_census_tract</th>\n",
       "      <th>pickup_community_area</th>\n",
       "      <th>dropoff_community_area</th>\n",
       "      <th>...</th>\n",
       "      <th>extras</th>\n",
       "      <th>trip_total</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>company</th>\n",
       "      <th>pickup_latitude</th>\n",
       "      <th>pickup_longitude</th>\n",
       "      <th>pickup_location</th>\n",
       "      <th>dropoff_latitude</th>\n",
       "      <th>dropoff_longitude</th>\n",
       "      <th>dropoff_location</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ade155d8f730a13006affc42f26dbd82b0d83ea9</td>\n",
       "      <td>f3e1a9085537bee3447c67580b4d30b827f97b8a3fa638...</td>\n",
       "      <td>2013-07-12 20:30:00+00:00</td>\n",
       "      <td>2013-07-12 20:30:00+00:00</td>\n",
       "      <td>480.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.86</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>Chicago Elite Cab Corp.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3fe2116d5628b3a9a6547b8ce1f480afb8f3578e</td>\n",
       "      <td>d95b99518116b5f943d75828e78c02e668ec6add7d28ba...</td>\n",
       "      <td>2013-07-12 13:45:00+00:00</td>\n",
       "      <td>2013-07-12 13:45:00+00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.05</td>\n",
       "      <td>No Charge</td>\n",
       "      <td>Chicago Elite Cab Corp.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0343559825f045e6341e4ab0a9a648c6fdafdd3a</td>\n",
       "      <td>6dcd43a953f0c1bdfc49fe8260b9504af2cc45eefdfc22...</td>\n",
       "      <td>2013-09-28 05:15:00+00:00</td>\n",
       "      <td>2013-09-28 05:30:00+00:00</td>\n",
       "      <td>480.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.82</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>Chicago Elite Cab Corp. (Chicago Carriag</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>a9db489cb175bbc82894dd74c109e61c4028cbaf</td>\n",
       "      <td>335e5b94c1eb05d4b83164f78b0246b7ce44188a32d029...</td>\n",
       "      <td>2013-07-12 23:15:00+00:00</td>\n",
       "      <td>2013-07-12 23:30:00+00:00</td>\n",
       "      <td>1500.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21.15</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>Choice Taxi Association</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>545fa4c18ec5f6eea912e6891c4795ac0772b87c</td>\n",
       "      <td>bd54bee5660726988fc54355ab54fc7cfd5c3917d911f9...</td>\n",
       "      <td>2013-09-27 12:00:00+00:00</td>\n",
       "      <td>2013-09-27 12:00:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.25</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>Chicago Elite Cab Corp. (Chicago Carriag</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 unique_key  \\\n",
       "0  ade155d8f730a13006affc42f26dbd82b0d83ea9   \n",
       "1  3fe2116d5628b3a9a6547b8ce1f480afb8f3578e   \n",
       "2  0343559825f045e6341e4ab0a9a648c6fdafdd3a   \n",
       "3  a9db489cb175bbc82894dd74c109e61c4028cbaf   \n",
       "4  545fa4c18ec5f6eea912e6891c4795ac0772b87c   \n",
       "\n",
       "                                             taxi_id  \\\n",
       "0  f3e1a9085537bee3447c67580b4d30b827f97b8a3fa638...   \n",
       "1  d95b99518116b5f943d75828e78c02e668ec6add7d28ba...   \n",
       "2  6dcd43a953f0c1bdfc49fe8260b9504af2cc45eefdfc22...   \n",
       "3  335e5b94c1eb05d4b83164f78b0246b7ce44188a32d029...   \n",
       "4  bd54bee5660726988fc54355ab54fc7cfd5c3917d911f9...   \n",
       "\n",
       "       trip_start_timestamp        trip_end_timestamp  trip_seconds  \\\n",
       "0 2013-07-12 20:30:00+00:00 2013-07-12 20:30:00+00:00         480.0   \n",
       "1 2013-07-12 13:45:00+00:00 2013-07-12 13:45:00+00:00           0.0   \n",
       "2 2013-09-28 05:15:00+00:00 2013-09-28 05:30:00+00:00         480.0   \n",
       "3 2013-07-12 23:15:00+00:00 2013-07-12 23:30:00+00:00        1500.0   \n",
       "4 2013-09-27 12:00:00+00:00 2013-09-27 12:00:00+00:00           NaN   \n",
       "\n",
       "   trip_miles pickup_census_tract dropoff_census_tract pickup_community_area  \\\n",
       "0         0.0                None                 None                  None   \n",
       "1         0.0                None                 None                  None   \n",
       "2         0.0                None                 None                  None   \n",
       "3         0.0                None                 None                  None   \n",
       "4         0.0                None                 None                  None   \n",
       "\n",
       "  dropoff_community_area  ...  extras  trip_total  payment_type  \\\n",
       "0                   None  ...     0.0       10.86   Credit Card   \n",
       "1                   None  ...     0.0       11.05     No Charge   \n",
       "2                   None  ...     0.0       11.82   Credit Card   \n",
       "3                   None  ...     0.0       21.15   Credit Card   \n",
       "4                   None  ...     0.0       40.25   Credit Card   \n",
       "\n",
       "                                    company  pickup_latitude pickup_longitude  \\\n",
       "0                   Chicago Elite Cab Corp.             None             None   \n",
       "1                   Chicago Elite Cab Corp.             None             None   \n",
       "2  Chicago Elite Cab Corp. (Chicago Carriag             None             None   \n",
       "3                   Choice Taxi Association             None             None   \n",
       "4  Chicago Elite Cab Corp. (Chicago Carriag             None             None   \n",
       "\n",
       "  pickup_location dropoff_latitude dropoff_longitude dropoff_location  \n",
       "0            None             None              None             None  \n",
       "1            None             None              None             None  \n",
       "2            None             None              None             None  \n",
       "3            None             None              None             None  \n",
       "4            None             None              None             None  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"chicago_taxi_trips\" dataset\n",
    "dataset_ref = client.dataset(\"chicago_taxi_trips\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)\n",
    "\n",
    "# Construct a reference to the \"taxi_trips\" table\n",
    "table_ref = dataset_ref.table(\"taxi_trips\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises\n",
    "\n",
    "### 1) How can you predict the demand for taxis?\n",
    "\n",
    "Say you work for a taxi company, and you're interested in predicting the demand for taxis.  Towards this goal, you'd like to create a plot that shows a rolling average of the daily number of taxi trips.  Amend the (partial) query below to return a DataFrame with two columns:\n",
    "- `trip_date` - contains one entry for each date from January 1, 2016, to December 31, 2017.\n",
    "- `avg_num_trips` - shows the average number of daily trips, calculated over a window including the value for the current date, along with the values for the preceding 15 days and the following 15 days, as long as the days fit within the two-year time frame.  For instance, when calculating the value in this column for January 5, 2016, the window will include the number of trips for the preceding 4 days, the current date, and the following 15 days.\n",
    "\n",
    "This query is partially completed for you, and you need only write the part that calculates the `avg_num_trips` column.  Note that this query uses a common table expression (CTE); if you need to review how to use CTEs, you're encouraged to check out [this tutorial](https://www.kaggle.com/dansbecker/as-with) in the [Intro to SQL](https://www.kaggle.com/learn/intro-to-sql) micro-course."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trip_date</th>\n",
       "      <th>avg_num_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-01-01</td>\n",
       "      <td>80461.937500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01-02</td>\n",
       "      <td>80150.647059</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-01-03</td>\n",
       "      <td>79419.611111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-01-04</td>\n",
       "      <td>79810.421053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-01-05</td>\n",
       "      <td>80293.900000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    trip_date  avg_num_trips\n",
       "0  2016-01-01   80461.937500\n",
       "1  2016-01-02   80150.647059\n",
       "2  2016-01-03   79419.611111\n",
       "3  2016-01-04   79810.421053\n",
       "4  2016-01-05   80293.900000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"1_TaxiDemand\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Fill in the blank below\n",
    "avg_num_trips_query = \"\"\"\n",
    "                      WITH trips_by_day AS\n",
    "                      (\n",
    "                          SELECT DATE(trip_start_timestamp) AS trip_date,\n",
    "                              COUNT(*) as num_trips\n",
    "                          FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n",
    "                          WHERE trip_start_timestamp >= '2016-01-01' AND trip_start_timestamp < '2018-01-01'\n",
    "                          GROUP BY trip_date\n",
    "                          ORDER BY trip_date\n",
    "                      )\n",
    "                      SELECT trip_date,\n",
    "                          AVG(num_trips)\n",
    "                          OVER (\n",
    "                              ORDER BY trip_date\n",
    "                              ROWS BETWEEN 15 PRECEDING AND 15 FOLLOWING\n",
    "                          ) AS avg_num_trips\n",
    "                      FROM trips_by_day\n",
    "                      \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_1.hint()\n",
    "#q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Can you separate and order trips by community area?\n",
    "\n",
    "The query below returns a DataFrame with three columns from the table: `pickup_community_area`, `trip_start_timestamp`, and `trip_end_timestamp`.  \n",
    "\n",
    "Amend the query to return an additional column called `trip_number` which shows the order in which the trips were taken from their respective community areas.  So, the first trip of the day originating from community area 1 should receive a value of 1; the second trip of the day from the same area should receive a value of 2.  Likewise, the first trip of the day from community area 2 should receive a value of 1, and so on.\n",
    "\n",
    "Note that there are many numbering functions that can be used to solve this problem (depending on how you want to deal with trips that started at the same time from the same community area); to answer this question, please use the **RANK()** function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pickup_community_area</th>\n",
       "      <th>trip_start_timestamp</th>\n",
       "      <th>trip_end_timestamp</th>\n",
       "      <th>trip_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20.0</td>\n",
       "      <td>2017-05-01 00:45:00+00:00</td>\n",
       "      <td>2017-05-01 01:00:00+00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20.0</td>\n",
       "      <td>2017-05-01 02:00:00+00:00</td>\n",
       "      <td>2017-05-01 02:00:00+00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20.0</td>\n",
       "      <td>2017-05-01 02:00:00+00:00</td>\n",
       "      <td>2017-05-01 02:15:00+00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20.0</td>\n",
       "      <td>2017-05-01 03:15:00+00:00</td>\n",
       "      <td>2017-05-01 03:30:00+00:00</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20.0</td>\n",
       "      <td>2017-05-01 03:45:00+00:00</td>\n",
       "      <td>2017-05-01 04:00:00+00:00</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   pickup_community_area      trip_start_timestamp        trip_end_timestamp  \\\n",
       "0                   20.0 2017-05-01 00:45:00+00:00 2017-05-01 01:00:00+00:00   \n",
       "1                   20.0 2017-05-01 02:00:00+00:00 2017-05-01 02:00:00+00:00   \n",
       "2                   20.0 2017-05-01 02:00:00+00:00 2017-05-01 02:15:00+00:00   \n",
       "3                   20.0 2017-05-01 03:15:00+00:00 2017-05-01 03:30:00+00:00   \n",
       "4                   20.0 2017-05-01 03:45:00+00:00 2017-05-01 04:00:00+00:00   \n",
       "\n",
       "   trip_number  \n",
       "0            1  \n",
       "1            2  \n",
       "2            2  \n",
       "3            4  \n",
       "4            5  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"2_CommunityArea\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Amend the query below\n",
    "trip_number_query = \"\"\"\n",
    "                    SELECT pickup_community_area,\n",
    "                        trip_start_timestamp,\n",
    "                        trip_end_timestamp,\n",
    "                        RANK()\n",
    "                            OVER (\n",
    "                                PARTITION BY pickup_community_area\n",
    "                                ORDER BY trip_start_timestamp\n",
    "                            ) AS trip_number\n",
    "                    FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n",
    "                    WHERE DATE(trip_start_timestamp) = '2017-05-01'\n",
    "                    \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_2.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_2.hint()\n",
    "#q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) How much time elapses between trips?\n",
    "\n",
    "The (partial) query in the code cell below shows, for each trip in the selected time frame, the corresponding `taxi_id`, `trip_start_timestamp`, and `trip_end_timestamp`. \n",
    "\n",
    "Your task in this exercise is to edit the query to include an additional `prev_break` column that shows the length of the break (in minutes) that the driver had before each trip started (this corresponds to the time between `trip_start_timestamp` of the current trip and `trip_end_timestamp` of the previous trip).  Partition the calculation by `taxi_id`, and order the results within each partition by `trip_start_timestamp`.\n",
    "\n",
    "Some sample results are shown below, where all rows correspond to the same driver (or `taxi_id`).  Take the time now to make sure that the values in the `prev_break` column make sense to you!\n",
    "\n",
    "![first_commands](https://i.imgur.com/qjvQzg8.png)\n",
    "\n",
    "Note that the first trip of the day for each driver should have a value of **NaN** (not a number) in the `prev_break` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>taxi_id</th>\n",
       "      <th>trip_start_timestamp</th>\n",
       "      <th>trip_end_timestamp</th>\n",
       "      <th>prev_break</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>020f09fafe503bfb0f58dc3e7d9f3ee60fa94c1c1473cf...</td>\n",
       "      <td>2017-05-01 16:00:00+00:00</td>\n",
       "      <td>2017-05-01 16:00:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...</td>\n",
       "      <td>2017-05-01 06:00:00+00:00</td>\n",
       "      <td>2017-05-01 06:00:00+00:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...</td>\n",
       "      <td>2017-05-01 06:15:00+00:00</td>\n",
       "      <td>2017-05-01 06:15:00+00:00</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...</td>\n",
       "      <td>2017-05-01 06:30:00+00:00</td>\n",
       "      <td>2017-05-01 06:45:00+00:00</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...</td>\n",
       "      <td>2017-05-01 06:45:00+00:00</td>\n",
       "      <td>2017-05-01 06:45:00+00:00</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             taxi_id  \\\n",
       "0  020f09fafe503bfb0f58dc3e7d9f3ee60fa94c1c1473cf...   \n",
       "1  19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...   \n",
       "2  19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...   \n",
       "3  19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...   \n",
       "4  19746bb16bdf4cfc8816650883cc6b6f8a7401ceb73a61...   \n",
       "\n",
       "       trip_start_timestamp        trip_end_timestamp  prev_break  \n",
       "0 2017-05-01 16:00:00+00:00 2017-05-01 16:00:00+00:00         NaN  \n",
       "1 2017-05-01 06:00:00+00:00 2017-05-01 06:00:00+00:00         NaN  \n",
       "2 2017-05-01 06:15:00+00:00 2017-05-01 06:15:00+00:00        15.0  \n",
       "3 2017-05-01 06:30:00+00:00 2017-05-01 06:45:00+00:00        15.0  \n",
       "4 2017-05-01 06:45:00+00:00 2017-05-01 06:45:00+00:00         0.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.3333333333333333, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_BreakTime\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Fill in the blanks below\n",
    "break_time_query = \"\"\"\n",
    "                   SELECT taxi_id,\n",
    "                       trip_start_timestamp,\n",
    "                       trip_end_timestamp,\n",
    "                       TIMESTAMP_DIFF(\n",
    "                           trip_start_timestamp, \n",
    "                           LAG(trip_end_timestamp) \n",
    "                               OVER (\n",
    "                                   PARTITION BY taxi_id \n",
    "                                   ORDER BY trip_start_timestamp\n",
    "                               ),\n",
    "                               MINUTE\n",
    "                       ) as prev_break\n",
    "                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n",
    "                   WHERE DATE(trip_start_timestamp) = '2017-05-01' \n",
    "                   \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_3.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"3_BreakTime\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> The `TIMESTAMP_DIFF()` function takes three arguments, where the first (`trip_start_timestamp`) and the last (`MINUTE`) are provided for you.  This function provides the time difference (in minutes) of the timestamps in the first two arguments. You need only fill in the second argument, which should use the **LAG()** function to pull the timestamp corresponding to the end of the previous trip (for the same `taxi_id`)."
      ],
      "text/plain": [
       "Hint: The `TIMESTAMP_DIFF()` function takes three arguments, where the first (`trip_start_timestamp`) and the last (`MINUTE`) are provided for you.  This function provides the time difference (in minutes) of the timestamps in the first two arguments. You need only fill in the second argument, which should use the **LAG()** function to pull the timestamp corresponding to the end of the previous trip (for the same `taxi_id`)."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "q_3.hint()\n",
    "#q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Keep going\n",
    "\n",
    "Move on to learn how to query **[nested and repeated data](https://www.kaggle.com/alexisbcook/nested-and-repeated-data)**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**[Advanced SQL Home Page](https://www.kaggle.com/learn/advanced-sql)**\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [Learn Discussion forum](https://www.kaggle.com/learn-forum) to chat with other Learners.*"
   ]
  }
 ],
 "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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}