SQL_and_BigQuery_day5_AS_AND_WITH.ipynb

{"cells":[{"metadata":{},"cell_type":"markdown","source":"**[SQL Home Page](https://www.kaggle.com/learn/intro-to-sql)**\n\n---\n"},{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\nYou 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. "},{"metadata":{"trusted":true},"cell_type":"code","source":"from collections import Counter","execution_count":1,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql.ex5 import *\nprint(\"Setup Complete\")","execution_count":2,"outputs":[{"output_type":"stream","text":"Using Kaggle's public dataset BigQuery integration.\nSetup Complete\n","name":"stdout"}]},{"metadata":{},"cell_type":"markdown","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."},{"metadata":{"trusted":true},"cell_type":"code","source":"from google.cloud import bigquery\n\n# Create a \"Client\" object\nclient = bigquery.Client()\n\n# Construct a reference to the \"chicago_taxi_trips\" dataset\ndataset_ref = client.dataset(\"chicago_taxi_trips\", project=\"bigquery-public-data\")\n\n# API request - fetch the dataset\ndataset = client.get_dataset(dataset_ref)","execution_count":3,"outputs":[{"output_type":"stream","text":"Using Kaggle's public dataset BigQuery integration.\n","name":"stdout"}]},{"metadata":{},"cell_type":"markdown","source":"# Exercises\n\nYou are curious how much slower traffic moves when traffic volume is high. This involves a few steps.\n\n### 1) Find the data\nBefore 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."},{"metadata":{"trusted":true},"cell_type":"code","source":"count=0\nfor table in list(client.list_tables(dataset)):\n    count+=1\n    print(table.table_id)","execution_count":4,"outputs":[{"output_type":"stream","text":"taxi_trips\n","name":"stdout"}]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code here to find the table name\n\n\n# Construct a reference to the \"comments\" table\ntable_ref = dataset_ref.table(\"taxi_trips\")\n\n# API request - fetch the table\ntable = client.get_table(table_ref)\n","execution_count":5,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Write the table name as a string below\ntable_name = \"taxi_trips\"\n\n# Check your answer\nq_1.check()","execution_count":6,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 1, \"learnTutorialId\": 81, \"questionId\": \"1_GetTableName\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"For the solution, uncomment the line below."},{"metadata":{"trusted":true},"cell_type":"code","source":"#q_1.solution()","execution_count":7,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 2) Peek at the data\n\nUse 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. "},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\ndf=client.list_rows(table,max_results=10).to_dataframe()","execution_count":8,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Counter(df['pickup_census_tract'])","execution_count":9,"outputs":[{"output_type":"execute_result","execution_count":9,"data":{"text/plain":"Counter({None: 10})"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"df","execution_count":10,"outputs":[{"output_type":"execute_result","execution_count":10,"data":{"text/plain":"                                 unique_key  \\\n0  4403e7519ce3054aac9dd71d79eaca3eb1b2557a   \n1  8fc26ada98bad7968d348de14d3fe70038f666b2   \n2  2a12be590cbedfa410b087e3f45adb34e14a1b8d   \n3  e66cacbb4450645b7a6e2af8d912c707639fe408   \n4  99c29121e8d7b8743d1452d4a10b58cb01bae634   \n5  a1a859a8c06ee88eda9db68f83e434b5dfe51c95   \n6  ba01af0426cf85d47d086275522bfc50efdefcbd   \n7  8ab619f1c4cf3926796c2468cdcea8807dfee53d   \n8  a45d388af6f43d34b5c263b5c923c880912d358f   \n9  2f86ec33122412e08e856603bfcceccc53692897   \n\n                                             taxi_id  \\\n0  28aae32c537012df2679016ea69889f7b76700cc8da53c...   \n1  40073e4bbfadf8904891079ddb48e72014816a3ae6405f...   \n2  6b29392d56c7d58fe76ec1513ab5c0afc54acee011e973...   \n3  80e91e768cf85470d7c7f2f9a87058f6eeddde89b168bd...   \n4  e51e2c30caec952b40b8329a68b498e18ce8a1f40fa75c...   \n5  0d47aaa43eb375f721661a91f68791c44d7c070b4b264d...   \n6  2407ff99e367ceb469a8cc6663e6ad6ec51fb0f401d370...   \n7  5dbc79d49f385fc3e536f3dd4e32bd7e3145e14d2c616f...   \n8  b02980a1f1cf5e8f64e56a911bd2bc7e0fb733bf6e465f...   \n9  42ca78689b10fede6f807e85b7dab63d0920ccf01d7695...   \n\n       trip_start_timestamp        trip_end_timestamp  trip_seconds  \\\n0 2014-02-18 08:00:00+00:00 2014-02-18 08:30:00+00:00          1620   \n1 2014-03-17 22:45:00+00:00 2014-03-17 23:00:00+00:00           840   \n2 2014-01-23 21:30:00+00:00 2014-01-23 22:00:00+00:00          1380   \n3 2014-01-26 20:45:00+00:00 2014-01-26 21:15:00+00:00          1500   \n4 2014-01-10 09:30:00+00:00 2014-01-10 10:00:00+00:00          2400   \n5 2014-03-19 23:00:00+00:00 2014-03-19 23:30:00+00:00          1320   \n6 2014-01-16 15:00:00+00:00 2014-01-16 17:00:00+00:00          7320   \n7 2014-01-03 18:45:00+00:00 2014-01-03 19:30:00+00:00          2340   \n8 2014-03-12 22:45:00+00:00 2014-03-12 23:00:00+00:00          1080   \n9 2014-01-14 08:00:00+00:00 2014-01-14 08:45:00+00:00          2040   \n\n   trip_miles pickup_census_tract dropoff_census_tract  pickup_community_area  \\\n0        7.10                None                 None                     14   \n1        8.00                None                 None                     14   \n2        6.90                None                 None                     14   \n3        0.77                None                 None                     14   \n4       10.40                None                 None                     14   \n5        1.24                None                 None                     14   \n6       13.40                None                 None                     14   \n7        0.99                None                 None                     14   \n8       13.30                None                 None                     14   \n9        7.10                None                 None                     14   \n\n  dropoff_community_area       ...         extras  trip_total  payment_type  \\\n0                   None       ...            3.0       21.64          Cash   \n1                   None       ...            0.0       18.45          Cash   \n2                   None       ...            0.0       17.85   Credit Card   \n3                   None       ...            0.0       19.65          Cash   \n4                   None       ...            0.0       27.45          Cash   \n5                   None       ...            0.0       25.85          Cash   \n6                   None       ...            0.0       55.05          Cash   \n7                   None       ...            1.0       26.05          Cash   \n8                   None       ...            0.0       27.45          Cash   \n9                   None       ...            0.0       20.45          Cash   \n\n   company  pickup_latitude pickup_longitude  \\\n0     None        41.968069       -87.721559   \n1     None        41.968069       -87.721559   \n2     None        41.968069       -87.721559   \n3     None        41.968069       -87.721559   \n4     None        41.968069       -87.721559   \n5     None        41.968069       -87.721559   \n6     None        41.968069       -87.721559   \n7     None        41.968069       -87.721559   \n8     None        41.968069       -87.721559   \n9     None        41.968069       -87.721559   \n\n                    pickup_location  dropoff_latitude  dropoff_longitude  \\\n0  POINT (-87.7215590627 41.968069)              None               None   \n1  POINT (-87.7215590627 41.968069)              None               None   \n2  POINT (-87.7215590627 41.968069)              None               None   \n3  POINT (-87.7215590627 41.968069)              None               None   \n4  POINT (-87.7215590627 41.968069)              None               None   \n5  POINT (-87.7215590627 41.968069)              None               None   \n6  POINT (-87.7215590627 41.968069)              None               None   \n7  POINT (-87.7215590627 41.968069)              None               None   \n8  POINT (-87.7215590627 41.968069)              None               None   \n9  POINT (-87.7215590627 41.968069)              None               None   \n\n  dropoff_location  \n0             None  \n1             None  \n2             None  \n3             None  \n4             None  \n5             None  \n6             None  \n7             None  \n8             None  \n9             None  \n\n[10 rows x 23 columns]","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>4403e7519ce3054aac9dd71d79eaca3eb1b2557a</td>\n      <td>28aae32c537012df2679016ea69889f7b76700cc8da53c...</td>\n      <td>2014-02-18 08:00:00+00:00</td>\n      <td>2014-02-18 08:30:00+00:00</td>\n      <td>1620</td>\n      <td>7.10</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>3.0</td>\n      <td>21.64</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>8fc26ada98bad7968d348de14d3fe70038f666b2</td>\n      <td>40073e4bbfadf8904891079ddb48e72014816a3ae6405f...</td>\n      <td>2014-03-17 22:45:00+00:00</td>\n      <td>2014-03-17 23:00:00+00:00</td>\n      <td>840</td>\n      <td>8.00</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>18.45</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2a12be590cbedfa410b087e3f45adb34e14a1b8d</td>\n      <td>6b29392d56c7d58fe76ec1513ab5c0afc54acee011e973...</td>\n      <td>2014-01-23 21:30:00+00:00</td>\n      <td>2014-01-23 22:00:00+00:00</td>\n      <td>1380</td>\n      <td>6.90</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>17.85</td>\n      <td>Credit Card</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>e66cacbb4450645b7a6e2af8d912c707639fe408</td>\n      <td>80e91e768cf85470d7c7f2f9a87058f6eeddde89b168bd...</td>\n      <td>2014-01-26 20:45:00+00:00</td>\n      <td>2014-01-26 21:15:00+00:00</td>\n      <td>1500</td>\n      <td>0.77</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>19.65</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>99c29121e8d7b8743d1452d4a10b58cb01bae634</td>\n      <td>e51e2c30caec952b40b8329a68b498e18ce8a1f40fa75c...</td>\n      <td>2014-01-10 09:30:00+00:00</td>\n      <td>2014-01-10 10:00:00+00:00</td>\n      <td>2400</td>\n      <td>10.40</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>27.45</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>a1a859a8c06ee88eda9db68f83e434b5dfe51c95</td>\n      <td>0d47aaa43eb375f721661a91f68791c44d7c070b4b264d...</td>\n      <td>2014-03-19 23:00:00+00:00</td>\n      <td>2014-03-19 23:30:00+00:00</td>\n      <td>1320</td>\n      <td>1.24</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>25.85</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>ba01af0426cf85d47d086275522bfc50efdefcbd</td>\n      <td>2407ff99e367ceb469a8cc6663e6ad6ec51fb0f401d370...</td>\n      <td>2014-01-16 15:00:00+00:00</td>\n      <td>2014-01-16 17:00:00+00:00</td>\n      <td>7320</td>\n      <td>13.40</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>55.05</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>8ab619f1c4cf3926796c2468cdcea8807dfee53d</td>\n      <td>5dbc79d49f385fc3e536f3dd4e32bd7e3145e14d2c616f...</td>\n      <td>2014-01-03 18:45:00+00:00</td>\n      <td>2014-01-03 19:30:00+00:00</td>\n      <td>2340</td>\n      <td>0.99</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>1.0</td>\n      <td>26.05</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>a45d388af6f43d34b5c263b5c923c880912d358f</td>\n      <td>b02980a1f1cf5e8f64e56a911bd2bc7e0fb733bf6e465f...</td>\n      <td>2014-03-12 22:45:00+00:00</td>\n      <td>2014-03-12 23:00:00+00:00</td>\n      <td>1080</td>\n      <td>13.30</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>27.45</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>2f86ec33122412e08e856603bfcceccc53692897</td>\n      <td>42ca78689b10fede6f807e85b7dab63d0920ccf01d7695...</td>\n      <td>2014-01-14 08:00:00+00:00</td>\n      <td>2014-01-14 08:45:00+00:00</td>\n      <td>2040</td>\n      <td>7.10</td>\n      <td>None</td>\n      <td>None</td>\n      <td>14</td>\n      <td>None</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>20.45</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.968069</td>\n      <td>-87.721559</td>\n      <td>POINT (-87.7215590627 41.968069)</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>10 rows × 23 columns</p>\n</div>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"After deciding whether you see any important issues, run the code cell below."},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#q_2.solution()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 3) Determine when this data is from\n\nIf 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\nYour results should have two columns:\n- `year` - the year of the trips\n- `num_trips` - the number of trips in that year\n\nHints:\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 \\`)."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code goes here\nrides_per_year_query = \"\"\"\n                        SELECT EXTRACT(YEAR FROM trip_start_timestamp) AS year, COUNT(1) AS num_trips\n                        FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\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)\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nrides_per_year_query_job = client.query(rides_per_year_query) # Your code goes here\n\n# API request - run the query, and return a pandas DataFrame\nrides_per_year_result = rides_per_year_query_job.to_dataframe() # Your code goes here\n\n# View results\nprint(rides_per_year_result)\n\n# Check your answer\nq_3.check()","execution_count":11,"outputs":[{"output_type":"stream","text":"   year  num_trips\n0  2013   27217716\n1  2014   37395436\n2  2015   32385875\n3  2016   31759339\n4  2017   24988003\n5  2018   20732088\n6  2019    8524088\n","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 81, \"questionId\": \"3_YearDistrib\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"For a hint or the solution, uncomment the appropriate line below."},{"metadata":{"trusted":true},"cell_type":"code","source":"\n#q_3.hint()\n#q_3.solution()","execution_count":12,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 4) Dive slightly deeper\n\nIt's odd that 2017 had so few rides. You should wonder whether it was systematic under-reporting throughout the year, or whether some months are missing.  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:\n1. Use a **WHERE** clause to limit the query to data from 2017.\n2. Modify the query to extract the month rather than the year."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code goes here\nrides_per_month_query = \"\"\"\n\n                        SELECT EXTRACT(MONTH FROM trip_start_timestamp) AS month , COUNT(1) AS num_trips\n                        FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n                        WHERE EXTRACT(YEAR FROM trip_start_timestamp)  =2017\n                        GROUP BY month\n                        ORDER BY month\n            \n\"\"\" \n\n# Set up the query\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nrides_per_month_query_job = client.query(rides_per_month_query) # Your code goes here\n\n# API request - run the query, and return a pandas DataFrame\nrides_per_month_result = rides_per_month_query_job.to_dataframe() # Your code goes here\n\n# View results\nprint(rides_per_month_result)\n\n# Check your answer\nq_4.check()","execution_count":13,"outputs":[{"output_type":"stream","text":"    month  num_trips\n0       1    1972071\n1       2    1909802\n2       3    2362105\n3       4    2194702\n4       5    2323386\n5       6    2324472\n6       7    2054299\n7       8    2079861\n8       9    1950631\n9      10    2141197\n10     11    1907997\n11     12    1767480\n","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 81, \"questionId\": \"4_MonthDistrib\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"For a hint or the solution, uncomment the appropriate line below."},{"metadata":{"trusted":true},"cell_type":"code","source":"#q_4.hint()\n#q_4.solution()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 5) Write the query\n\nIt'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\nYour 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\nFor 2017, we're missing August and everything after. So 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\nYou 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\nThis is a much harder query than anything you've written so far.  Good luck!"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code goes here\nspeeds_query = \"\"\"\n               WITH RelevantRides AS\n               (\n                   SELECT trip_start_timestamp,trip_miles,trip_seconds\n                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n                   WHERE trip_seconds>0 AND trip_miles>0  AND  \n                   trip_start_timestamp > '2017-01-01' AND \n                   trip_start_timestamp < '2017-07-01' \n\n               )\n               SELECT EXTRACT(HOUR FROM trip_start_timestamp) AS hour_of_day, COUNT(1) AS num_trips, (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\n# Set up the query\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nspeeds_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\nspeeds_result = speeds_query_job.to_dataframe() # Your code here\n\n# View results\nprint(speeds_result)\n\n# Check your answer\nq_5.check()","execution_count":38,"outputs":[{"output_type":"stream","text":"    hour_of_day  num_trips    avg_mph\n0             0     319339  20.230524\n1             1     266529  18.937621\n2             2     210147  18.777070\n3             3     159668  20.158048\n4             4     122183  26.736014\n5             5     119312  30.769172\n6             6     182738  24.588313\n7             7     358406  17.735967\n8             8     541775  15.079892\n9             9     565548  16.543882\n10           10     525120  18.539614\n11           11     594603  18.928379\n12           12     622324  17.838745\n13           13     630181  17.671089\n14           14     622465  16.974239\n15           15     640430  15.688418\n16           16     701435  14.283888\n17           17     756627  12.462955\n18           18     768251  13.646810\n19           19     701064  16.642882\n20           20     598614  19.536777\n21           21     552726  20.433874\n22           22     501095  19.531374\n23           23     399587  19.877046\n","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 81, \"questionId\": \"5_TheLongQuery\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"For the solution, uncomment the appropriate line below."},{"metadata":{"trusted":true},"cell_type":"code","source":"### q_5.solution()","execution_count":39,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"That's a hard query. If you made good progress towards the solution, congratulations!"},{"metadata":{},"cell_type":"markdown","source":"# Keep going\n\nYou 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 SQL micro-course."},{"metadata":{},"cell_type":"markdown","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":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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.5"}},"nbformat":4,"nbformat_minor":1}