intro_to_sql/05-as-with.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[SQL Home Page](https://www.kaggle.com/learn/intro-to-sql)**\n",
    "\n",
    "---\n",
    "\n",
    "*This exercise involves you writing code, and we check it automatically to tell you if it's right. We're having a temporary problem with out checking infrastructure, causing a bar that says `None` in some cases when you have the right answer. We're sorry. We're fixing it. In the meantime, if you see a bar saying `None` that means you've done something good.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "You are getting to the point where you can own an analysis from beginning to end. So you'll do more data exploration in this exercise than you've done before.  Before you get started, run the following set-up code as usual. "
   ]
  },
  {
   "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.ex5 import *\n",
    "print(\"Setup Complete\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll work with a dataset about taxi trips in the city of Chicago. Run the cell below to fetch the `chicago_taxi_trips` dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    }
   ],
   "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)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises\n",
    "\n",
    "You are curious how much slower traffic moves when traffic volume is high. This involves a few steps.\n",
    "\n",
    "### 1) Find the data\n",
    "Before you can access the data, you need to find the table name with the data.\n",
    "\n",
    "*Hint*: Tab completion is helpful whenever you can't remember a command. Type `client.` and then hit the tab key. Don't forget the period before hitting tab."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['taxi_trips']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here to find the table name\n",
    "table_names = [x.table_id for x in client.list_tables(dataset)]\n",
    "table_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"1_GetTableName\", \"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": [
    "# Write the table name as a string below\n",
    "table_name = table_names[0]\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the solution, uncomment the line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Peek at the data\n",
    "\n",
    "Use the next code cell to peek at the top few rows of the data. Inspect the data and see if any issues with data quality are immediately obvious. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Your code here\n",
    "table_ref = dataset_ref.table(table_name)\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"taxi_trips\" table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After deciding whether you see any important issues, run the code cell below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3) Determine when this data is from\n",
    "\n",
    "If the data is sufficiently old, we might be careful before assuming the data is still relevant to traffic patterns today. Write a query that counts the number of trips in each year.  \n",
    "\n",
    "Your results should have two columns:\n",
    "- `year` - the year of the trips\n",
    "- `num_trips` - the number of trips in that year\n",
    "\n",
    "Hints:\n",
    "- When using **GROUP BY** and **ORDER BY**, you should refer to the columns by the alias `year` that you set at the top of the **SELECT** query.\n",
    "- The SQL code to **SELECT** the year from `trip_start_timestamp` is <code>SELECT EXTRACT(YEAR FROM trip_start_timestamp)</code>\n",
    "- The **FROM** field can be a little tricky until you are used to it.  The format is:\n",
    "    1. A backick (the symbol \\`).\n",
    "    2. The project name. In this case it is `bigquery-public-data`.\n",
    "    3. A period.\n",
    "    4. The dataset name. In this case, it is `chicago_taxi_trips`.\n",
    "    5. A period.\n",
    "    6. The table name. You used this as your answer in **1) Find the data**.\n",
    "    7. A backtick (the symbol \\`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   year  num_trips\n",
      "0  2013   27217716\n",
      "1  2014   37395436\n",
      "2  2015   32385875\n",
      "3  2016   31759339\n",
      "4  2017   24988003\n",
      "5  2018   20732088\n",
      "6  2019   16477365\n",
      "7  2020    1073417\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_YearDistrib\", \"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": [
    "# Your code goes here\n",
    "rides_per_year_query = \"\"\"WITH cte AS\n",
    "                          (\n",
    "                            SELECT EXTRACT(YEAR FROM trip_start_timestamp) AS year\n",
    "                              FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n",
    "                          )\n",
    "                          SELECT year, COUNT(1) AS num_trips\n",
    "                            FROM cte\n",
    "                            GROUP BY year\n",
    "                            ORDER BY year\n",
    "                       \"\"\"\n",
    "\n",
    "# Set up the query (cancel the query if it would use too much of \n",
    "# your quota)\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "rides_per_year_query_job = client.query(rides_per_year_query, job_config=safe_config) # Your code goes here\n",
    "\n",
    "# API request - run the query, and return a pandas DataFrame\n",
    "rides_per_year_result = rides_per_year_query_job.to_dataframe() # Your code goes here\n",
    "\n",
    "# View results\n",
    "print(rides_per_year_result)\n",
    "\n",
    "# Check your answer\n",
    "q_3.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a hint or the solution, uncomment the appropriate line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_3.hint()\n",
    "#q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4) Dive slightly deeper\n",
    "\n",
    "You'd like to take a closer look at rides from 2017.  Copy the query you used above in `rides_per_year_query` into the cell below for `rides_per_month_query`.  Then modify it in two ways:\n",
    "1. Use a **WHERE** clause to limit the query to data from 2017.\n",
    "2. Modify the query to extract the month rather than the year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    month  num_trips\n",
      "0       1    1972071\n",
      "1       2    1909802\n",
      "2       3    2362105\n",
      "3       4    2194702\n",
      "4       5    2323386\n",
      "5       6    2324472\n",
      "6       7    2054299\n",
      "7       8    2079861\n",
      "8       9    1950631\n",
      "9      10    2141197\n",
      "10     11    1907997\n",
      "11     12    1767480\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"4_MonthDistrib\", \"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": [
    "# Your code goes here\n",
    "rides_per_month_query =  \"\"\"WITH cte AS\n",
    "                            (\n",
    "                              SELECT EXTRACT(MONTH FROM trip_start_timestamp) AS month\n",
    "                                FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n",
    "                                WHERE EXTRACT(YEAR FROM trip_start_timestamp) = 2017\n",
    "                            )\n",
    "                            SELECT month, COUNT(1) AS num_trips\n",
    "                              FROM cte\n",
    "                              GROUP BY month\n",
    "                              ORDER BY month\n",
    "                         \"\"\" \n",
    "\n",
    "# Set up the query\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "rides_per_month_query_job = client.query(rides_per_month_query, job_config=safe_config) # Your code goes here\n",
    "\n",
    "# API request - run the query, and return a pandas DataFrame\n",
    "rides_per_month_result = rides_per_month_query_job.to_dataframe() # Your code goes here\n",
    "\n",
    "# View results\n",
    "print(rides_per_month_result)\n",
    "\n",
    "# Check your answer\n",
    "q_4.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a hint or the solution, uncomment the appropriate line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_4.hint()\n",
    "#q_4.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5) Write the query\n",
    "\n",
    "It's time to step up the sophistication of your queries.  Write a query that shows, for each hour of the day in the dataset, the corresponding number of trips and average speed.\n",
    "\n",
    "Your results should have three columns:\n",
    "- `hour_of_day` - sort by this column, which holds the result of extracting the hour from `trip_start_timestamp`.\n",
    "- `num_trips` - the count of the total number of trips in each hour of the day (e.g. how many trips were started between 6AM and 7AM, independent of which day it occurred on).\n",
    "- `avg_mph` - the average speed, measured in miles per hour, for trips that started in that hour of the day.  Average speed in miles per hour is calculated as `3600 * SUM(trip_miles) / SUM(trip_seconds)`. (The value 3600 is used to convert from seconds to hours.)\n",
    "\n",
    "Restrict your query to data meeting the following criteria:\n",
    "- a `trip_start_timestamp` between **2017-01-01** and **2017-07-01**\n",
    "- `trip_seconds` > 0 and `trip_miles` > 0\n",
    "\n",
    "You will use a common table expression (CTE) to select just the relevant rides.  Because this dataset is very big, this CTE should select only the columns you'll need to create the final output (though you won't actually create those in the CTE -- instead you'll create those in the later **SELECT** statement below the CTE).\n",
    "\n",
    "This is a much harder query than anything you've written so far.  Good luck!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    hour_of_day  num_trips    avg_mph\n",
      "0             0     319339  20.230524\n",
      "1             1     266529  18.937621\n",
      "2             2     210147  18.777070\n",
      "3             3     159668  20.158048\n",
      "4             4     122183  26.736014\n",
      "5             5     119312  30.769172\n",
      "6             6     182738  24.588313\n",
      "7             7     358406  17.735967\n",
      "8             8     541775  15.079892\n",
      "9             9     565548  16.543882\n",
      "10           10     525120  18.539614\n",
      "11           11     594603  18.928379\n",
      "12           12     622324  17.838745\n",
      "13           13     630181  17.671089\n",
      "14           14     622465  16.974239\n",
      "15           15     640430  15.688418\n",
      "16           16     701435  14.283888\n",
      "17           17     756627  12.462955\n",
      "18           18     768251  13.646810\n",
      "19           19     701064  16.642882\n",
      "20           20     598614  19.536777\n",
      "21           21     552726  20.433874\n",
      "22           22     501095  19.531374\n",
      "23           23     399587  19.877046\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"5_TheLongQuery\", \"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": [
    "# Your code goes here\n",
    "speeds_query = \"\"\"\n",
    "               WITH RelevantRides AS\n",
    "               (\n",
    "                   SELECT EXTRACT(HOUR FROM trip_start_timestamp) AS hour_of_day,\n",
    "                       trip_miles,\n",
    "                       trip_seconds\n",
    "                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n",
    "                   WHERE trip_start_timestamp > '2017-01-01'\n",
    "                       AND trip_start_timestamp < '2017-07-01'\n",
    "                       AND trip_seconds > 0\n",
    "                       AND trip_miles > 0\n",
    "               )\n",
    "               SELECT hour_of_day,\n",
    "                   COUNT(1) AS num_trips,\n",
    "                   3600 * SUM(trip_miles) / SUM(trip_seconds) AS avg_mph\n",
    "               FROM RelevantRides\n",
    "               GROUP BY hour_of_day\n",
    "               ORDER BY hour_of_day\n",
    "               \"\"\"\n",
    "# Set up the query\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "speeds_query_job = client.query(speeds_query, job_config=safe_config) # Your code here\n",
    "\n",
    "# API request - run the query, and return a pandas DataFrame\n",
    "speeds_result = speeds_query_job.to_dataframe() # Your code here\n",
    "\n",
    "# View results\n",
    "print(speeds_result)\n",
    "\n",
    "# Check your answer\n",
    "q_5.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the solution, uncomment the appropriate line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_5.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a hard query. If you made good progress towards the solution, congratulations!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Keep going\n",
    "\n",
    "You can write very complex queries now with a single data source. But nothing expands the horizons of SQL as much as the ability to combine or **JOIN** tables.\n",
    "\n",
    "**[Click here](https://www.kaggle.com/dansbecker/joining-data)** to start the last lesson in the Intro to SQL micro-course."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**[SQL Home Page](https://www.kaggle.com/learn/intro-to-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
}