Task 24:

Clean Trip Dataset

DuckDB

Prepare a cleaned trip dataset for further analysis. Keep only records with the following criteria:
- Records with specified timestamps
- tpep_dropoff_datetime is not earlier than tpep_pickup_datetime
- trip_distance is positive
- total_amount is nonnegative
- passenger_count is positive

Replace a missing payment_type with Unknown. Add trip duration in minutes and trip cost per mile. Return the first 100 cleaned records.

Expected result Columns: 8 · Rows: 100

tpep_pickup_datetime	tpep_dropoff_datetime	payment_type	passenger_count	trip_distance	total_amount	trip_duration_minutes	amount_per_mile

Sample rows open after wrong checks: 0 of 2

Write your query below and click "Check it!". Tables are described in the right pane.

Tutorial

By checking a query, you agree that it is stored without your account details and may be included in anonymized datasets, including for AI training. Learn more

The query result will appear here. Ctrl+Enter - Run query

DuckDB yellow_tripdata Dataset

About DuckDB

DuckDB is an embedded analytical database designed for fast queries over local and application data.

It supports SQL and runs inside an application without a separate server process. Columnar storage and query execution make DuckDB well suited for analyzing large datasets, aggregations, and processing CSV and Parquet files.

In this playground, DuckDB is used to practice SQL with a taxi trip dataset.

yellow_tripdata is a learning dataset containing New York City yellow taxi trips.

The table is useful for practicing filtering, grouping, sorting, date operations, and aggregate calculations in DuckDB.

All fields in this dataset allow NULL values. No primary key or additional constraints are defined.

More about the NYC Yellow Taxi database: schema, sample queries and all exercises →

yellow_tripdata Table

yellow_tripdata - yellow taxi trips.

VendorID taxi service provider identifier.
tpep_pickup_datetime passenger pickup date and time.
tpep_dropoff_datetime passenger drop-off date and time.
passenger_count number of passengers.
trip_distance trip distance.
RatecodeID rate code identifier.
store_and_fwd_flag flag indicating that trip data was stored before forwarding.
PULocationID pickup zone identifier.
DOLocationID drop-off zone identifier.
payment_type payment method identifier.
fare_amount trip fare excluding additional charges.
extra additional charges.
mta_tax MTA tax.
tip_amount tip amount.
tolls_amount toll charges.
improvement_surcharge transportation system improvement surcharge.
total_amount total trip cost.
congestion_surcharge congestion surcharge.
Airport_fee airport fee.

yellow_tripdata table structure
Column name	Type	NULL	Key	Default	Extra
VendorID	INTEGER	YES	[null]	[null]	[null]
tpep_pickup_datetime	TIMESTAMP	YES	[null]	[null]	[null]
tpep_dropoff_datetime	TIMESTAMP	YES	[null]	[null]	[null]
passenger_count	BIGINT	YES	[null]	[null]	[null]
trip_distance	DOUBLE	YES	[null]	[null]	[null]
RatecodeID	BIGINT	YES	[null]	[null]	[null]
store_and_fwd_flag	VARCHAR	YES	[null]	[null]	[null]
PULocationID	INTEGER	YES	[null]	[null]	[null]
DOLocationID	INTEGER	YES	[null]	[null]	[null]
payment_type	BIGINT	YES	[null]	[null]	[null]
fare_amount	DOUBLE	YES	[null]	[null]	[null]
extra	DOUBLE	YES	[null]	[null]	[null]
mta_tax	DOUBLE	YES	[null]	[null]	[null]
tip_amount	DOUBLE	YES	[null]	[null]	[null]
tolls_amount	DOUBLE	YES	[null]	[null]	[null]
improvement_surcharge	DOUBLE	YES	[null]	[null]	[null]
total_amount	DOUBLE	YES	[null]	[null]	[null]
congestion_surcharge	DOUBLE	YES	[null]	[null]	[null]
Airport_fee	DOUBLE	YES	[null]	[null]	[null]