nhtsa_traffic_fatalities_state.ipynb

{
  "cells": [
    {
      "metadata": {
        "_cell_guid": "892e4766-ec60-4e04-841d-8bee2ddf5cba",
        "_uuid": "fdc18884a7717170aee090a74d286950a369ebc8"
      },
      "cell_type": "markdown",
      "source": "# Get Started\n\nAfter forking this notebook, run the code in the following cell:"
    },
    {
      "metadata": {
        "_cell_guid": "97ff21a3-e21d-4eac-99f0-434337df239d",
        "_uuid": "648483445c532732bbfc664ae32f961bebc3369c",
        "collapsed": true,
        "trusted": true
      },
      "cell_type": "code",
      "source": "# import package with helper functions \nimport bq_helper\n\n# create a helper object for this dataset\naccidents = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n                                   dataset_name=\"nhtsa_traffic_fatalities\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "scrolled": true,
        "_uuid": "940359ddf0eeac5f8f434b00e16f73bf9c51f09d",
        "trusted": true,
        "collapsed": true
      },
      "cell_type": "code",
      "source": "accidents.table_schema(\"vehicle_2015\")",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "f202fc66-31d4-4f78-8603-fdac21d8d3ca",
        "_uuid": "40e94f6a75f096799b5e2f51ab0c9ac9863c0b24"
      },
      "cell_type": "markdown",
      "source": "Then write the code to answer the questions below"
    },
    {
      "metadata": {
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
      },
      "cell_type": "markdown",
      "source": "# Questions\n\n#### 1) Which hours of the day do the most accidents occur during?\n* Return a table showing how many accidents occurred in each hour of the day in 2015, sorted by the the number of accidents which occurred each hour. Use either the accident_2015 or accident_2016 table for this, and the timestamp_of_crash column (there is an hour_of_crash column, but if you use that one you won't get a chance to practice with dates).\n\n**Hint:** You will probably want to use the [EXTRACT() function](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#extract_1) for this.\n"
    },
    {
      "metadata": {
        "scrolled": false,
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "trusted": true,
        "collapsed": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\"SELECT COUNT(consecutive_number), \n                  EXTRACT(HOUR FROM timestamp_of_crash)\n            FROM `bigquery-public-data.nhtsa_traffic_fatalities.accident_2015`\n            GROUP BY EXTRACT(HOUR FROM timestamp_of_crash)\n            ORDER BY COUNT(consecutive_number) DESC\n        \"\"\"\naccidents_by_hour = accidents.query_to_pandas_safe(query)\nprint(accidents_by_hour)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "03b31732-290b-4685-9578-79903a98c004",
        "_uuid": "3ad56a7bbbb9197040721e77e3e506321e5de069"
      },
      "cell_type": "markdown",
      "source": "#### 2) Which state has the most hit and runs?\n* Return a table with the number of vehicles registered in each state that were involved in hit-and-run accidents, sorted by the number of hit and runs. Use either the vehicle_2015 or vehicle_2016 table for this, especially the registration_state_name and hit_and_run columns (it may be helpful to view the hit_and_run column to understand its contents).\n"
    },
    {
      "metadata": {
        "_cell_guid": "bd07ec8d-0369-46d3-a4fb-e9aaa7f83eb0",
        "_uuid": "c77efc001b887f3dc1e6c22fb1d9c0e8134f00ae",
        "trusted": true,
        "collapsed": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\" SELECT registration_state_name, COUNT(consecutive_number)\n            FROM `bigquery-public-data.nhtsa_traffic_fatalities.vehicle_2016`\n            WHERE hit_and_run = \"Yes\"\n            GROUP BY registration_state_name\n            ORDER BY COUNT(consecutive_number) DESC\n\"\"\"\nhit_and_runs_per_state = accidents.query_to_pandas_safe(query)\nprint(hit_and_runs_per_state)",
      "execution_count": null,
      "outputs": []
    },
    {
      "metadata": {
        "_cell_guid": "01828b85-515b-4b17-b4e9-ec4fac545158",
        "_uuid": "9e6e7e8d31495735dd39520f157f48bdef27f8ca"
      },
      "cell_type": "markdown",
      "source": "---\n# Keep Going\n[Click here](https://www.kaggle.com/dansbecker/with-as) to learn how *WITH-AS* clauses  can clean up your code and help you construct more complex queries.\n\n# Feedback\nBring any questions or feedback to the [Learn Discussion Forum](kaggle.com/learn-forum).\n\n----\n\n*This tutorial is part of the [SQL Series](https://www.kaggle.com/learn/sql) on Kaggle Learn.*"
    }
  ],
  "metadata": {
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}