Example: 3.5 million taxi trips over HTTPS

中文

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.

This guide assumes you have completed Your first 10 minutes 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:

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

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 ⌘↵:

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:

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?

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.

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.

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:

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. Download 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:

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:

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.
• 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, which also documents every column.