# Analyzing the NYC Restaurant Inspections Dataset (/free-sample-datasets/nyc-restaurants)



New York City's Department of Health publishes the results of every restaurant inspection. This
tutorial covers the schema, loading the data into Firebolt, and sample SQL queries for extracting
insights from the records.

## Dataset schema [#dataset-schema]

The dataset contains 27 columns:

| Column Name             | Data Type | Description                            |
| ----------------------- | --------- | -------------------------------------- |
| `camis`                 | INTEGER   | Unique restaurant ID                   |
| `dba`                   | TEXT      | Business name ("doing business as")    |
| `boro`                  | TEXT      | Borough                                |
| `building`              | TEXT      | Building number                        |
| `street`                | TEXT      | Street                                 |
| `zipcode`               | TEXT      | ZIP code                               |
| `phone`                 | TEXT      | Phone number                           |
| `cuisine_description`   | TEXT      | Cuisine type                           |
| `inspection_date`       | TEXT      | Date of inspection                     |
| `action`                | TEXT      | Action taken as a result of inspection |
| `violation_code`        | TEXT      | Violation code                         |
| `violation_description` | TEXT      | Violation description                  |
| `critical_flag`         | TEXT      | Whether the violation was critical     |
| `score`                 | NUMERIC   | Inspection score                       |
| `grade`                 | TEXT      | Letter grade                           |
| `grade_date`            | DATE      | Date the grade was issued              |
| `record_date`           | DATE      | Date the record was created            |
| `inspection_type`       | TEXT      | Type of inspection                     |
| `latitude`              | NUMERIC   | Latitude                               |
| `longitude`             | NUMERIC   | Longitude                              |
| `community_board`       | TEXT      | Community board                        |
| `council_district`      | TEXT      | Council district                       |
| `census_tract`          | TEXT      | Census tract                           |
| `bin`                   | TEXT      | Building identification number         |
| `bbl`                   | TEXT      | Borough-block-lot identifier           |
| `nta`                   | TEXT      | Neighborhood tabulation area           |
| `location_point1`       | TEXT      | Location point                         |

## 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_restaurant_inspections FROM 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyc_restaurant_inspections/parquet/'
WITH PATTERN="*.parquet" AUTO_CREATE=TRUE TYPE=PARQUET;
```

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

Create an external table over the Parquet files in S3:

```sql
CREATE EXTERNAL TABLE ex_nyc_restaurant_inspections (
  camis INTEGER,
  dba TEXT,
  boro TEXT,
  building TEXT,
  street TEXT,
  zipcode TEXT,
  phone TEXT,
  cuisine_description TEXT,
  inspection_date TEXT,
  action TEXT,
  violation_code TEXT,
  violation_description TEXT,
  critical_flag TEXT,
  score NUMERIC,
  grade TEXT,
  grade_date DATE,
  record_date DATE,
  inspection_type TEXT,
  latitude NUMERIC,
  longitude NUMERIC,
  community_board TEXT,
  council_district TEXT,
  census_tract TEXT,
  bin TEXT,
  bbl TEXT,
  nta TEXT,
  location_point1 TEXT
) URL = 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nyc_restaurant_inspections/parquet/'
OBJECT_PATTERN = '*.parquet'
TYPE = (PARQUET);
```

Create the internal table:

```sql
CREATE TABLE nyc_restaurant_inspections(
  camis INTEGER,
  dba TEXT,
  boro TEXT,
  building TEXT,
  street TEXT,
  zipcode TEXT,
  phone TEXT,
  cuisine_description TEXT,
  inspection_date TEXT,
  action TEXT,
  violation_code TEXT,
  violation_description TEXT,
  critical_flag TEXT,
  score NUMERIC,
  grade TEXT,
  grade_date DATE,
  record_date DATE,
  inspection_type TEXT,
  latitude NUMERIC,
  longitude NUMERIC,
  community_board TEXT,
  council_district TEXT,
  census_tract TEXT,
  bin TEXT,
  bbl TEXT,
  nta TEXT,
  location_point1 TEXT
);
```

Load the data from the external table:

```sql
INSERT INTO
  nyc_restaurant_inspections
SELECT
  camis,
  dba,
  boro,
  building,
  street,
  zipcode,
  phone,
  cuisine_description,
  inspection_date,
  action,
  violation_code,
  violation_description,
  critical_flag,
  score,
  grade,
  grade_date,
  record_date,
  inspection_type,
  latitude,
  longitude,
  community_board,
  council_district,
  census_tract,
  bin,
  bbl,
  nta,
  location_point1
FROM
  ex_nyc_restaurant_inspections;
```

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

### Total inspections by borough [#total-inspections-by-borough]

```sql
SELECT boro, COUNT(*) as total_inspections
FROM nyc_restaurant_inspections
GROUP BY boro
ORDER BY total_inspections DESC;
```

### Top 10 most common cuisine types [#top-10-most-common-cuisine-types]

```sql
SELECT cuisine_description, COUNT(*) as count
FROM nyc_restaurant_inspections
GROUP BY cuisine_description
ORDER BY count DESC
LIMIT 10;
```

### Top 10 highest-scoring restaurants [#top-10-highest-scoring-restaurants]

```sql
SELECT dba, boro, max(score) as score
FROM nyc_restaurant_inspections
GROUP BY ALL
ORDER BY score DESC
LIMIT 10;
```

### Violations by critical flag [#violations-by-critical-flag]

```sql
SELECT critical_flag, COUNT(*) as count
FROM nyc_restaurant_inspections
GROUP BY critical_flag;
```

### Average inspection score by cuisine type (top 10) [#average-inspection-score-by-cuisine-type-top-10]

```sql
SELECT cuisine_description, AVG(score) as avg_score
FROM nyc_restaurant_inspections
GROUP BY cuisine_description
ORDER BY avg_score DESC
LIMIT 10;
```

Customize these queries based on your specific analytical objectives.

<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>