NYC Yellow Taxi dataset (DuckDB): the yellow_tripdata table and SQL exercises

yellow_tripdata holds the New York City yellow taxi trips of January 2024, almost 3 million rows, loaded into DuckDB for analytical SQL. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

• 1 table, 19 columns
• 2,964,624 trips
• January 2024
• 19 SQL exercises

What is NYC Yellow Taxi

The data comes from the trip records that the New York City Taxi and Limousine Commission (TLC) publishes every month. Each row is one trip: pickup and drop-off time, distance, number of passengers, pickup and drop-off zones, payment type and every part of the fare.

DuckDB is an embedded analytical database with columnar storage, so aggregations over millions of rows run in a fraction of a second. That makes the dataset a good place to practice real analytics: time series, distributions, percentiles and data cleaning.

What's inside

All the data is in one table, yellow_tripdata. Every column allows NULL, and there are no keys or constraints. PULocationID and DOLocationID are TLC taxi zone numbers. A few trips have pickup times outside January 2024 and some have zero or negative amounts: real data needs cleaning, and some exercises are about exactly that.

How much data the tables hold:

Table structure

Click a table to see its columns, a sample row and its keys.

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

Trip duration: DuckDB's date_diff between pickup and drop-off.

SELECT tpep_pickup_datetime,
       date_diff('minute', tpep_pickup_datetime, tpep_dropoff_datetime) AS minutes,
       trip_distance, total_amount
FROM yellow_tripdata
WHERE tpep_pickup_datetime >= '2024-01-01'
ORDER BY tpep_pickup_datetime
LIMIT 3;

A quick count: DuckDB's GROUP BY ALL groups by every non-aggregated column.

SELECT store_and_fwd_flag, COUNT(*) AS trips
FROM yellow_tripdata
GROUP BY ALL
ORDER BY trips DESC;

SQL exercises by topic

There are 19 exercises on this dataset, from overall statistics to time series and data quality checks. Solutions are checked automatically on a real DuckDB. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

• Analytical queries 19

Where to start

The first exercises on the NYC Yellow Taxi database:

1. Overall Trip Statistics
2. Average Fare by Payment Type
3. Longest Trips
4. Monthly Statistics
5. Most Popular Pickup Zones
6. Compare Tips by Payment Type
7. Top Pickup Zones by Month
8. Passenger Trip Analysis
9. Trip Efficiency by Distance Range
10. Zones with Unusually High Cost per Mile

All NYC Yellow Taxi exercises →