# Analyzing the NYC Parking Violations Dataset (/free-sample-datasets/nyc-parking-violations) New York City's extensive parking-violation records provide rich analytical opportunities. This guide explains the dataset schema, demonstrates loading the data into Firebolt, and provides sample SQL queries for analysis. ## Understanding the schema [#understanding-the-schema] The dataset contains 44 columns capturing violation details: | Column Name | Data Type | Description | | ----------------------------------- | --------- | ------------------------------------- | | `summons` | Integer | Unique identifier for each violation | | `plateid` | Text | License plate identifier | | `registration` | Text | Vehicle registration identifier | | `plate` | Text | License plate information | | `issue_date` | Date | Date the violation was issued | | `violation_code` | Integer | Code indicating the violation type | | `vehicle_body` | Text | Vehicle body type | | `vehicle_make` | Text | Vehicle manufacturer | | `issuing_agency` | Text | Agency responsible for issuing | | `street_code1` | Integer | Primary street code | | `street_code2` | Integer | Secondary street code | | `street_code3` | Integer | Another secondary street code | | `vehicle_expiration` | Text | Vehicle registration expiration | | `violation_location` | Integer | Violation location code | | `violation_precinct` | Integer | Precinct where the violation occurred | | `issuer_precinct` | Integer | Issuing officer precinct | | `issuer_code` | Integer | Issuing officer code | | `issuer_command` | Text | Issuing officer command | | `issuer_squad` | Text | Issuing officer squad | | `violation_time` | Text | Time of the violation | | `time_first_observed` | Text | When the violation was first observed | | `violation_county` | Text | County of the violation | | `violation_infront_opposite` | Text | Position relative to the address | | `house_integer` | Text | House number identifier | | `street_name` | Text | Street name | | `intersecting_street` | Text | Intersecting street name | | `date_first_observed` | Text | Date first observed | | `law_section` | Integer | Relevant law section | | `sub_division` | Text | Subdivision information | | `violation_legal_code` | Text | Legal code | | `days_parking_in_effect` | Text | Days when parking restrictions apply | | `from_hours_in_effect` | Text | Starting time of restrictions | | `to_hours_in_effect` | Text | Ending time of restrictions | | `vehicle_color` | Text | Vehicle color | | `unregistered_vehicle` | Text | Unregistered vehicle indicator | | `vehicle_year` | Text | Vehicle manufacture year | | `meter_integer` | Text | Meter number | | `feet_from_curb` | Integer | Distance from curb in feet | | `violation_post_code` | Text | Postal code | | `violation_description` | Text | Violation description | | `no_standing_or_stopping_violation` | Text | Standing/stopping violation indicator | | `hydrant_violation` | Text | Hydrant violation indicator | | `double_parking_violation` | Text | Double parking indicator | ## Loading data into Firebolt [#loading-data-into-firebolt] There are two ways to load the data. ### Option 1: COPY FROM [#option-1-copy-from] Let Firebolt infer the schema and create the table automatically: ```sql COPY INTO nyc_parkingviolations FROM 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nycparking/parquet/' WITH PATTERN="*.parquet" AUTO_CREATE=TRUE TYPE=PARQUET; ``` ### Option 2: External table [#option-2-external-table] First create an external table over the Parquet files in S3: ```sql CREATE EXTERNAL TABLE ex_nyc_parkingviolations ( summons Integer, plateid Text, registration Text, plate Text, issue_date TEXT, violation_code Integer, vehicle_body Text, vehicle_make Text, issuing_agency Text, street_code1 Integer, street_code2 Integer, street_code3 Integer, vehicle_expiration Text, violation_location Integer, violation_precinct Integer, issuer_precinct Integer, issuer_code Integer, issuer_command Text, issuer_squad Text, violation_time Text, time_first_observed Text, violation_county Text, violation_infront_opposit Text, house_integer Text, street_name Text, intersecting_street Text, date_first_observed Integer, law_section Integer, sub_division Text, violation_legal_code Text, days_parking_in_effect Text, from_hours_in_effect Text, to_hours_in_effect Text, vehicle_color Text, unregistered_vehicle Integer, vehicle_year Text, meter_integer Text, feet_from_curb Integer, violation_post_code Text, violation_description Text, no_standing_or_stopping_violation Text, hydrant_violation Text, double_parking_violation Text ) URL = 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nycparking/parquet/' OBJECT_PATTERN = '*.parquet' TYPE = (PARQUET); ``` Then create the internal table: ```sql CREATE TABLE nyc_parkingviolations ( summons Integer, plateid Text, registration Text, plate Text, issue_date Date, violation_code Integer, vehicle_body Text, vehicle_make Text, issuing_agency Text, street_code1 Integer, street_code2 Integer, street_code3 Integer, vehicle_expiration Integer, violation_location Integer, violation_precinct Integer, issuer_precinct Integer, issuer_code Integer, issuer_command Text, issuer_squad Text, violation_time Text, time_first_observed Text, violation_county Text, violation_infront_opposit Text, house_integer Text, street_name Text, intersecting_street Text, date_first_observed Integer, law_section Integer, sub_division Text, violation_legal_code Text, days_parking_in_effect Text, from_hours_in_effect Text, to_hours_in_effect Text, vehicle_color Text, unregistered_vehicle Integer, vehicle_year Text, meter_integer Text, feet_from_curb Integer, violation_post_code Text, violation_description Text, no_standing_or_stopping_violation Text, hydrant_violation Text, double_parking_violation Text ); ``` Finally, insert from the external table, converting `issue_date` to a timestamp: ```sql INSERT INTO nyc_parkingviolations SELECT summons , plateid , registration , plate , TO_TIMESTAMP(issue_date,'YYYY-MM-DD') , violation_code , vehicle_body , vehicle_make , issuing_agency , street_code1 , street_code2 , street_code3 , vehicle_expiration , violation_location , violation_precinct , issuer_precinct , issuer_code , issuer_command , issuer_squad , violation_time , time_first_observed , violation_county , violation_infront_opposit , house_integer , street_name , intersecting_street , date_first_observed , law_section , sub_division , violation_legal_code , days_parking_in_effect , from_hours_in_effect , to_hours_in_effect , vehicle_color , unregistered_vehicle , vehicle_year , meter_integer , feet_from_curb , violation_post_code , violation_description , no_standing_or_stopping_violation , hydrant_violation , double_parking_violation FROM ex_nyc_parkingviolations; ``` ## Sample SQL queries for analysis [#sample-sql-queries-for-analysis] ### Count of violations by violation code [#count-of-violations-by-violation-code] Identify the most frequently occurring parking violations. ```sql SELECT violation_code, violation_description, COUNT(*) AS violation_count FROM nyc_parkingviolations GROUP BY violation_code, violation_description ORDER BY violation_count DESC; ``` ### Violations per day [#violations-per-day] Reveal temporal patterns in violation issuance. ```sql SELECT issue_date, COUNT(*) AS daily_violations FROM nyc_parkingviolations GROUP BY issue_date ORDER BY issue_date; ``` ### Top vehicle makes with the most violations [#top-vehicle-makes-with-the-most-violations] See which vehicle manufacturers are most frequently cited. ```sql SELECT vehicle_make, COUNT(*) AS violation_count FROM nyc_parkingviolations GROUP BY vehicle_make ORDER BY violation_count DESC LIMIT 10; ``` ### Violations by issuing agency [#violations-by-issuing-agency] Categorize violations by the responsible enforcement agency. ```sql SELECT issuing_agency, COUNT(*) AS violation_count FROM nyc_parkingviolations GROUP BY issuing_agency ORDER BY violation_count DESC; ``` The dataset invites further exploration to uncover insights about enforcement patterns and vehicle characteristics. Spin up an engine and load this dataset yourself — Firebolt is free to try.