exercise-analytic-functions.ipynb

{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.10.10","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**This notebook is an exercise in the [Advanced SQL](https://www.kaggle.com/learn/advanced-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/alexisbcook/analytic-functions).**\n\n---\n","metadata":{}},{"cell_type":"markdown","source":"# Introduction\n\nHere, you'll use window functions to answer questions about the [Chicago Taxi Trips](https://www.kaggle.com/chicago/chicago-taxi-trips-bq) dataset.\n\nBefore you get started, run the code cell below to set everything up.","metadata":{}},{"cell_type":"code","source":"# Get most recent checking code\n!pip install -U -t /kaggle/working/ git+https://github.com/Kaggle/learntools.git\n# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql_advanced.ex2 import *\nprint(\"Setup Complete\")","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:24:13.669312Z","iopub.execute_input":"2023-06-07T20:24:13.669825Z","iopub.status.idle":"2023-06-07T20:24:44.877335Z","shell.execute_reply.started":"2023-06-07T20:24:13.669802Z","shell.execute_reply":"2023-06-07T20:24:44.876184Z"},"trusted":true},"execution_count":1,"outputs":[{"name":"stdout","text":"Collecting git+https://github.com/Kaggle/learntools.git\n  Cloning https://github.com/Kaggle/learntools.git to /tmp/pip-req-build-cjegfjf5\n  Running command git clone --filter=blob:none --quiet https://github.com/Kaggle/learntools.git /tmp/pip-req-build-cjegfjf5\n  Resolved https://github.com/Kaggle/learntools.git to commit 69bc6daec79619690e758841dc2df35708d226c8\n  Preparing metadata (setup.py) ... \u001b[?25ldone\n\u001b[?25hBuilding wheels for collected packages: learntools\n  Building wheel for learntools (setup.py) ... \u001b[?25ldone\n\u001b[?25h  Created wheel for learntools: filename=learntools-0.3.4-py3-none-any.whl size=268981 sha256=9a5faf7506dd59ac57716b1a68afb8f5a20e29906fc9ff01f60d8fc95aaba7f7\n  Stored in directory: /tmp/pip-ephem-wheel-cache-fqvfv0u1/wheels/2f/6c/3c/aa9f50cfb5a862157cb4c7a5b34881828cf45404698255dced\nSuccessfully built learntools\nInstalling collected packages: learntools\nSuccessfully installed learntools-0.3.4\n\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\n\u001b[0mUsing Kaggle's public dataset BigQuery integration.\nSetup Complete\n","output_type":"stream"}]},{"cell_type":"markdown","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.","metadata":{}},{"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)\n\n# Construct a reference to the \"taxi_trips\" table\ntable_ref = dataset_ref.table(\"taxi_trips\")\n\n# API request - fetch the table\ntable = client.get_table(table_ref)\n\n# Preview the first five lines of the table\nclient.list_rows(table, max_results=5).to_dataframe()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:24:52.024269Z","iopub.execute_input":"2023-06-07T20:24:52.024611Z","iopub.status.idle":"2023-06-07T20:24:52.976828Z","shell.execute_reply.started":"2023-06-07T20:24:52.024584Z","shell.execute_reply":"2023-06-07T20:24:52.975639Z"},"trusted":true},"execution_count":3,"outputs":[{"name":"stdout","text":"Using Kaggle's public dataset BigQuery integration.\n","output_type":"stream"},{"execution_count":3,"output_type":"execute_result","data":{"text/plain":"                                 unique_key  \\\n0  eca8235a5d0b4d9a3bd9989f914b173640609419   \n1  e0eeb8c6cb83698f72a18765c2d9941427ef998b   \n2  bbfe965bb457bdb92e2ee3819b96954f5b96a069   \n3  bbf29b19ecbf5b8a6584fc1138288b83d6c89332   \n4  8492e8e5a4e942bef9e8907f6673f0d2c4b96341   \n\n                                             taxi_id  \\\n0  4243ab34318764eb5ca51054af503c610b50579594cef6...   \n1  4243ab34318764eb5ca51054af503c610b50579594cef6...   \n2  cfbd7f55fa135ea0d514faa2bd53482c5e1397ced1ebcb...   \n3  330454be209c63a9c8aec48a98de3f263757034a18e6eb...   \n4  6cf92a358750dd211c7d4338426b4fb1ee6178ac7cf522...   \n\n       trip_start_timestamp        trip_end_timestamp  trip_seconds  \\\n0 2013-02-23 22:45:00+00:00 2013-02-23 23:00:00+00:00           660   \n1 2013-02-24 00:15:00+00:00 2013-02-24 00:45:00+00:00          1920   \n2 2014-06-10 21:45:00+00:00 2014-06-10 22:00:00+00:00           360   \n3 2014-06-10 20:30:00+00:00 2014-06-10 20:45:00+00:00           600   \n4 2013-02-16 01:30:00+00:00 2013-02-16 01:45:00+00:00           480   \n\n   trip_miles  pickup_census_tract  dropoff_census_tract  \\\n0         1.7         1.703108e+10          1.703108e+10   \n1        11.8                  NaN                   NaN   \n2         1.5                  NaN                   NaN   \n3         2.3                  NaN                   NaN   \n4         3.9                  NaN                   NaN   \n\n   pickup_community_area  dropoff_community_area  ...  extras  trip_total  \\\n0                    8.0                     8.0  ...     1.5       11.35   \n1                    7.0                     NaN  ...     0.0       27.25   \n2                    NaN                     NaN  ...     0.0        8.65   \n3                    NaN                     NaN  ...     0.0        8.25   \n4                    NaN                     NaN  ...     0.0       11.05   \n\n   payment_type  company  pickup_latitude pickup_longitude  \\\n0   Credit Card     None        41.892073       -87.628874   \n1          Cash     None        41.922686       -87.649489   \n2   Credit Card     None              NaN              NaN   \n3          Cash     None              NaN              NaN   \n4          Cash     None              NaN              NaN   \n\n                        pickup_location  dropoff_latitude  dropoff_longitude  \\\n0  POINT (-87.6288741572 41.8920726347)         41.907492          -87.63576   \n1  POINT (-87.6494887289 41.9226862843)               NaN                NaN   \n2                                  None               NaN                NaN   \n3                                  None               NaN                NaN   \n4                                  None               NaN                NaN   \n\n                       dropoff_location  \n0  POINT (-87.6357600901 41.9074919303)  \n1                                  None  \n2                                  None  \n3                                  None  \n4                                  None  \n\n[5 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>eca8235a5d0b4d9a3bd9989f914b173640609419</td>\n      <td>4243ab34318764eb5ca51054af503c610b50579594cef6...</td>\n      <td>2013-02-23 22:45:00+00:00</td>\n      <td>2013-02-23 23:00:00+00:00</td>\n      <td>660</td>\n      <td>1.7</td>\n      <td>1.703108e+10</td>\n      <td>1.703108e+10</td>\n      <td>8.0</td>\n      <td>8.0</td>\n      <td>...</td>\n      <td>1.5</td>\n      <td>11.35</td>\n      <td>Credit Card</td>\n      <td>None</td>\n      <td>41.892073</td>\n      <td>-87.628874</td>\n      <td>POINT (-87.6288741572 41.8920726347)</td>\n      <td>41.907492</td>\n      <td>-87.63576</td>\n      <td>POINT (-87.6357600901 41.9074919303)</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>e0eeb8c6cb83698f72a18765c2d9941427ef998b</td>\n      <td>4243ab34318764eb5ca51054af503c610b50579594cef6...</td>\n      <td>2013-02-24 00:15:00+00:00</td>\n      <td>2013-02-24 00:45:00+00:00</td>\n      <td>1920</td>\n      <td>11.8</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7.0</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>27.25</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>41.922686</td>\n      <td>-87.649489</td>\n      <td>POINT (-87.6494887289 41.9226862843)</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>bbfe965bb457bdb92e2ee3819b96954f5b96a069</td>\n      <td>cfbd7f55fa135ea0d514faa2bd53482c5e1397ced1ebcb...</td>\n      <td>2014-06-10 21:45:00+00:00</td>\n      <td>2014-06-10 22:00:00+00:00</td>\n      <td>360</td>\n      <td>1.5</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>8.65</td>\n      <td>Credit Card</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>bbf29b19ecbf5b8a6584fc1138288b83d6c89332</td>\n      <td>330454be209c63a9c8aec48a98de3f263757034a18e6eb...</td>\n      <td>2014-06-10 20:30:00+00:00</td>\n      <td>2014-06-10 20:45:00+00:00</td>\n      <td>600</td>\n      <td>2.3</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>8.25</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>8492e8e5a4e942bef9e8907f6673f0d2c4b96341</td>\n      <td>6cf92a358750dd211c7d4338426b4fb1ee6178ac7cf522...</td>\n      <td>2013-02-16 01:30:00+00:00</td>\n      <td>2013-02-16 01:45:00+00:00</td>\n      <td>480</td>\n      <td>3.9</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>...</td>\n      <td>0.0</td>\n      <td>11.05</td>\n      <td>Cash</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 23 columns</p>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"# Exercises\n\n### 1) How can you predict the demand for taxis?\n\nSay 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 March 31, 2016.\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 3 days and the following 3 days, as long as the days fit within the three-month time frame.  For instance, when calculating the value in this column for January 3, 2016, the window will include the number of trips for the preceding 2 days, the current date, and the following 3 days.\n\nThis 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) course.","metadata":{}},{"cell_type":"code","source":"# Fill in the blank below\navg_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 < '2016-04-01'\n                      GROUP BY trip_date\n                      )\n                      SELECT trip_date,\n                          AVG(num_trips) \n                          OVER (\n                               ORDER BY trip_date\n                               ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING\n                               ) AS avg_num_trips\n                      FROM trips_by_day\n                      \"\"\"\n\n# Check your answer\nq_1.check()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:30:06.967657Z","iopub.execute_input":"2023-06-07T20:30:06.967981Z","iopub.status.idle":"2023-06-07T20:30:07.892418Z","shell.execute_reply.started":"2023-06-07T20:30:06.967956Z","shell.execute_reply":"2023-06-07T20:30:07.890632Z"},"trusted":true},"execution_count":6,"outputs":[{"output_type":"display_data","data":{"text/plain":"    trip_date  avg_num_trips\n0  2016-03-04   93811.000000\n1  2016-03-20   92998.857143\n2  2016-01-03   73287.833333\n3  2016-03-03   93109.857143\n4  2016-02-22   90943.428571","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-03-04</td>\n      <td>93811.000000</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2016-03-20</td>\n      <td>92998.857143</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2016-01-03</td>\n      <td>73287.833333</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2016-03-03</td>\n      <td>93109.857143</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2016-02-22</td>\n      <td>90943.428571</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}},{"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.3333333333333333, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"1_TaxiDemand\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_1.hint()\n#q_1.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2) Can you separate and order trips by community area?\n\nThe query below returns a DataFrame with three columns from the table: `pickup_community_area`, `trip_start_timestamp`, and `trip_end_timestamp`.  \n\nAmend 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\nNote 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.","metadata":{}},{"cell_type":"code","source":"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) = '2013-10-03' \n                    \"\"\"\n\ntrip_number_result = client.query(trip_number_query).result().to_dataframe()\nq_2.check()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:30:45.830058Z","iopub.execute_input":"2023-06-07T20:30:45.830406Z","iopub.status.idle":"2023-06-07T20:30:54.408695Z","shell.execute_reply.started":"2023-06-07T20:30:45.830384Z","shell.execute_reply":"2023-06-07T20:30:54.407372Z"},"trusted":true},"execution_count":7,"outputs":[{"output_type":"display_data","data":{"text/plain":"   pickup_community_area      trip_start_timestamp        trip_end_timestamp  \\\n0                   53.0 2013-10-03 08:45:00+00:00 2013-10-03 09:30:00+00:00   \n1                   53.0 2013-10-03 15:15:00+00:00 2013-10-03 15:30:00+00:00   \n2                   57.0 2013-10-03 04:45:00+00:00 2013-10-03 05:00:00+00:00   \n3                   57.0 2013-10-03 15:30:00+00:00 2013-10-03 16:30:00+00:00   \n4                   57.0 2013-10-03 18:45:00+00:00 2013-10-03 19:00:00+00:00   \n\n   trip_number  \n0            1  \n1            2  \n2            1  \n3            2  \n4            3  ","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>53.0</td>\n      <td>2013-10-03 08:45:00+00:00</td>\n      <td>2013-10-03 09:30:00+00:00</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>53.0</td>\n      <td>2013-10-03 15:15:00+00:00</td>\n      <td>2013-10-03 15:30:00+00:00</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>57.0</td>\n      <td>2013-10-03 04:45:00+00:00</td>\n      <td>2013-10-03 05:00:00+00:00</td>\n      <td>1</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>57.0</td>\n      <td>2013-10-03 15:30:00+00:00</td>\n      <td>2013-10-03 16:30:00+00:00</td>\n      <td>2</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>57.0</td>\n      <td>2013-10-03 18:45:00+00:00</td>\n      <td>2013-10-03 19:00:00+00:00</td>\n      <td>3</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}},{"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.3333333333333333, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"2_CommunityArea\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_2.hint()\n#q_2.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3) How much time elapses between trips?\n\nThe (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\nYour 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\nSome 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://storage.googleapis.com/kaggle-media/learn/images/qjvQzg8.png)\n\nNote that the first trip of the day for each driver should have a value of **NaN** (not a number) in the `prev_break` column.","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"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, 1) OVER (PARTITION BY taxi_id ORDER BY trip_start_timestamp), \n                           MINUTE) as prev_break\n                   FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`\n                   WHERE DATE(trip_start_timestamp) = '2013-10-03' \n                   \"\"\"\n\nbreak_time_result = client.query(break_time_query).result().to_dataframe()\n\n# Check your answer\nq_3.check()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_3.hint()\n#q_3.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Keep going\n\nMove on to learn how to query **[nested and repeated data](https://www.kaggle.com/alexisbcook/nested-and-repeated-data)**.","metadata":{}},{"cell_type":"markdown","source":"---\n\n\n\n\n*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/advanced-sql/discussion) to chat with other learners.*","metadata":{}}]}