# NYC Traffic Dataset Analysis: A SQL Warm-Up (/free-sample-datasets/nyc-traffic)



New York City maintains comprehensive traffic records through its Department of Transportation. This
tutorial walks through analyzing NYC traffic patterns using SQL queries in Firebolt.

## Dataset schema [#dataset-schema]

| Column Name  | Data Type | Description                                |
| ------------ | --------- | ------------------------------------------ |
| `requestid`  | Bigint    | Unique identifier for each record          |
| `boro`       | Text      | Borough the traffic data is from           |
| `yr`         | Int       | Year of the data                           |
| `month`      | Int       | Month of the data                          |
| `dd`         | Int       | Day of the data                            |
| `hh`         | Int       | Hour of the data                           |
| `mm`         | Int       | Minute of the data                         |
| `vol`        | Int       | Traffic volume                             |
| `segmentid`  | Bigint    | Unique identifier for each segment         |
| `wktgeom`    | Text      | Well-Known Text representation of geometry |
| `street`     | Text      | Street name                                |
| `fromstreet` | Text      | Starting street                            |
| `tostreet`   | Text      | Ending street                              |
| `direction`  | Text      | Traffic direction                          |

## Loading NYC traffic data into Firebolt [#loading-nyc-traffic-data-into-firebolt]

### Option 1: COPY FROM [#option-1-copy-from]

Let Firebolt infer the schema and create the table automatically:

```sql
COPY INTO nyc_traffic FROM 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyctraffic/parquet/'
WITH PATTERN="*.parquet" AUTO_CREATE=TRUE TYPE=PARQUET;
```

This generates the following table structure:

```sql
CREATE TABLE "nyc_traffic" ("requestid" bigint NULL, "boro" text NULL, "yr" integer NULL, "month" integer NULL, "dd" integer NULL, "hh" integer NULL, "mm" integer NULL, "vol" integer NULL, "segmentid" bigint NULL, "wktgeom" text NULL, "street" text NULL, "fromstreet" text NULL, "tostreet" text NULL, "direction" text NULL)
```

### Option 2: External table [#option-2-external-table]

Create an external table over the Parquet files in S3:

```sql
CREATE EXTERNAL TABLE ex_nyc_traffic (
  requestid bigint,
  boro Text,
  yr int,
  month int,
  dd int,
  hh int,
  mm int,
  vol int,
  segmentid bigint,
  wktgeom Text,
  street Text,
  fromstreet Text,
  tostreet Text,
  direction Text
) URL = 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyctraffic/parquet/' OBJECT_PATTERN = '*.parquet'
TYPE = (PARQUET);
```

Create the internal table with a primary index tuned for the queries below:

```sql
CREATE TABLE nyc_traffic (
  requestid bigint,
  boro Text,
  yr int,
  month int,
  dd int,
  hh int,
  mm int,
  vol int,
  segmentid bigint,
  wktgeom Text,
  street Text,
  fromstreet Text,
  tostreet Text,
  direction Text) PRIMARY INDEX yr,month,dd, hh, mm, boro, street;
```

Load the data:

```sql
INSERT INTO nyc_traffic SELECT * FROM ex_nyc_traffic;
```

## Sample SQL queries [#sample-sql-queries]

### Total traffic volume by segment and borough [#total-traffic-volume-by-segment-and-borough]

Identify the top 10 segments with the highest traffic volume.

```sql
SELECT boro, street, segmentid, SUM(vol) AS TotalTrafficVolume
FROM nyc_traffic
GROUP BY boro, street, segmentid
ORDER BY TotalTrafficVolume DESC
LIMIT 10;
```

### Traffic volume by direction [#traffic-volume-by-direction]

Break down traffic volume by direction.

```sql
SELECT direction, SUM(vol) AS TotalTrafficVolume
FROM nyc_traffic
GROUP BY direction;
```

### Count of records by month and year [#count-of-records-by-month-and-year]

Identify trends across month-year combinations.

```sql
SELECT yr, month, COUNT(*) AS RecordCount
FROM nyc_traffic
GROUP BY yr, month
ORDER BY yr, month;
```

### Busiest streets by total traffic volume [#busiest-streets-by-total-traffic-volume]

Find the top 10 busiest streets for infrastructure planning.

```sql
SELECT street, SUM(vol) AS TotalTrafficVolume
FROM nyc_traffic
GROUP BY street
ORDER BY TotalTrafficVolume DESC
LIMIT 10;
```

### Peak traffic hour [#peak-traffic-hour]

Find the hour with the highest traffic volume.

```sql
SELECT hh, SUM(vol) AS TotalTrafficVolume
FROM nyc_traffic
GROUP BY hh
ORDER BY TotalTrafficVolume DESC
LIMIT 1;
```

### Comprehensive traffic analysis [#comprehensive-traffic-analysis]

Combine year, borough, streets, and direction into one view.

```sql
SELECT yr, boro, street, fromstreet, tostreet, direction, SUM(vol) AS TotalTrafficVolume
FROM nyc_traffic
GROUP BY ALL
ORDER BY yr, TotalTrafficVolume DESC;
```

<InlineCta ctaLabel="Get started for free" ctaUrl="https://go.firebolt.io/signup">
  Spin up an engine and load this dataset yourself — Firebolt is free to try.
</InlineCta>