# Example: 3.5 million taxi trips over HTTPS

> Query a 3.5-million-row NYC taxi Parquet file over HTTPS with DuckDB SQL, then chart trips by hour and payment type in DuckLocal.

Source: https://ducklocal.app/docs/example-nyc-taxi

**[中文](zh/example-nyc-taxi.md)**

New York City publishes every yellow-cab trip as a Parquet file. January 2025
is one 59 MB file with 3,475,226 rows, and DuckLocal can query it where it
sits — no download step, no cluster, no import. This walkthrough takes it from
a first look to three charts and a dashboard, one query at a time. For the
broader file-first workflow, see [local data analysis with DuckDB SQL](https://ducklocal.app/use-cases/local-data-analysis/).

This guide assumes you have completed [Your first 10 minutes](tutorial.md) or are already familiar with the basics: pressing **⌘↵** (Ctrl+Enter on Windows and Linux) executes the query in the editor, and the results area includes a **Chart** tab.

The file:

```text
https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet
```

::: tip Network
The first query on an `https://` address makes DuckDB download its `httpfs`
extension, so you need to be online once. After that, each query reads only
the parts of the file it needs — a Parquet file keeps each column apart, and
DuckDB asks for byte ranges rather than the whole file.
:::

## 1. Look before you query

Paste this into a query tab and press **⌘↵**:

```sql
SELECT *
FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
LIMIT 100;
```

A hundred rows, twenty columns: pickup and drop-off times, distance, fare,
tip, pickup and drop-off zones, and how the rider paid. To see every column
with its type instead of scrolling sideways, `DESCRIBE` the same query:

```sql
DESCRIBE SELECT *
FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet';
```

The ones used below:

| Column | Type | Meaning |
| --- | --- | --- |
| `tpep_pickup_datetime` | TIMESTAMP | When the meter started |
| `fare_amount` | DOUBLE | The metered fare, in dollars |
| `tip_amount` | DOUBLE | The tip, in dollars — card tips only, see step 4 |
| `payment_type` | BIGINT | `1` credit card, `2` cash, `3` no charge, `4` dispute |

## 2. A month, hour by hour

How many trips started in each hour of January?

```sql
SELECT date_trunc('hour', tpep_pickup_datetime) AS hour,
       count(*) AS trips
FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
WHERE tpep_pickup_datetime >= '2025-01-01'
  AND tpep_pickup_datetime <  '2025-02-01'
GROUP BY 1
ORDER BY 1;
```

It aggregates all 3.5 million rows into 744 — one per hour of the month — in a
couple of seconds. Switch to **Chart**: the first column is a timestamp, so
DuckLocal draws an area chart over time. Every day shows as a peak, and the
peaks rise and fall with the week; the busiest hour of the month was 18:00 on
Thursday 16 January, with 13,288 trips.

Two details make the chart honest:

- **The `WHERE` clause.** Twenty-two trips in the file started outside
  January: 21 on New Year's Eve, one 44 seconds into February. Without the
  filter they add four stray hours at the ends of the chart.
- **`ORDER BY 1`.** Charts plot rows in the order the query returns them.
  `GROUP BY` promises no order, so without it the line would zigzag.

744 points is under the chart's 1,500-point limit, so every hour is drawn as
it is rather than averaged into buckets. Check the chart's title: it says how
many points were plotted.

## 3. The shape of a day

Fold the month into a single day: trips by hour of the day, 00:00 to 23:00.

```sql
SELECT format('{:02d}:00', hour(tpep_pickup_datetime)) AS pickup_hour,
       count(*) AS trips
FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
GROUP BY 1
ORDER BY 1;
```

**Chart** now draws 24 bars. The city is quietest at 04:00 (20,033 trips all
month) and busiest at 18:00 (267,951) — more than thirteen times as many.

::: warning Why the hour is text
The label is built with `format('{:02d}:00', …)` on purpose. The results
chart plots the **first numeric column** — and a plain `hour(…)` is a number.
It would become the bar heights, and you would get a tidy staircase from 0 to
23 instead of the trip counts. A text first column is a label, never a value.
Zero-padding (`04:00`, not `4:00`) keeps the text in the right order under
`ORDER BY`.
:::

## 4. Who tips?

Tips as a share of the fare, by how the trip was paid:

```sql
SELECT CASE payment_type
         WHEN 1 THEN 'Credit card' WHEN 2 THEN 'Cash'
         WHEN 3 THEN 'No charge'   WHEN 4 THEN 'Dispute'
         ELSE 'Other'
       END AS payment,
       round(100 * avg(tip_amount / fare_amount), 1) AS tip_pct,
       count(*) AS trips
FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
WHERE fare_amount > 0
GROUP BY 1
ORDER BY trips DESC;
```

| payment | tip_pct | trips |
| --- | --- | --- |
| Credit card | 26.8 | 2444292 |
| Other | 2.7 | 455288 |
| Cash | 0.0 | 375850 |
| Dispute | 0.0 | 38787 |
| No charge | 0.1 | 15493 |

Card riders tip about 27% of the fare; cash riders, apparently, nothing. That
is the data, not the riders: the meter only records tips paid by card. A cash
tip goes into the driver's hand and never reaches the file. Knowing where a
column comes from matters as much as the SQL.

`WHERE fare_amount > 0` keeps refunds and zero fares from dividing by zero or
flipping the sign. The chart plots `tip_pct` because it is the first numeric
column — put `trips` first to chart volume instead.

## 5. Keep it as a dashboard

Three queries worth running again belong in a [dashboard](dashboards.md).
Download [nyc-taxi.dash](https://ducklocal.app/docs/samples/nyc-taxi.dash):
it holds the hourly trend, the hour-of-day bars, the tip chart and a table of
the numbers behind it. Drag it onto the DuckLocal window, and it opens as a
tab that runs every query and draws every plot.

Because the SQL reads an absolute `https://` address, the file works however
and wherever it is opened. Before you share it, `ducklocal check` can run
every query to prove it works:

```bash
ducklocal check nyc-taxi.dash                         # syntax and references only
ducklocal check nyc-taxi.dash --database any.duckdb   # also runs each query
```

## 6. The same from a terminal

The CLI answers without a window, which is how an AI agent would ask:

```bash
ducklocal query --format md --sql "
  SELECT format('{:02d}:00', hour(tpep_pickup_datetime)) AS pickup_hour,
         count(*) AS trips
  FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
  GROUP BY 1 ORDER BY trips DESC LIMIT 3"
```

## Going further

- **Work offline, or faster.** Each query reads the columns it needs over the
  network again. To keep a copy, run the `SELECT *` from step 1 without
  `LIMIT` and use **Export Parquet…** — then drag the local file in, and it
  becomes a view you can query by name.
- **More months.** The files follow one pattern, `yellow_tripdata_YYYY-MM.parquet`.
  DuckDB reads a list of them as one table:
  `FROM read_parquet(['https://…/yellow_tripdata_2025-01.parquet', 'https://…/yellow_tripdata_2025-02.parquet'])`.
  A glob (`*`) does not work over plain HTTPS, because a web server cannot be
  listed; it does on [S3](s3.md).
- **Zones.** `PULocationID` and `DOLocationID` are taxi-zone numbers; the NYC
  Taxi & Limousine Commission (TLC) publishes the lookup table that names them.

The data is published by the
[TLC](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page),
which also documents every column.