Support the project! The site exists thanks to the community. If the help covers the expenses - the platform remains free. ☕ Support →
SQL code copied to buffer

SQLtest.online
Task 19: 
Analyze the yellow_tripdata table to identify and quantify various data quality issues. Your output should include the following columns:
- total_rows: Total number of rows in the dataset
- missing_pickup_count: Count of rows with missing tpep_pickup_datetime
- missing_dropoff_count: Count of rows with missing tpep_dropoff_datetime
- invalid_distance_count: Count of rows with invalid trip_distance
- invalid_amount_count: Count of rows with invalid total_amount
- invalid_passenger_count: Count of rows with invalid passenger_count
- invalid_time_count: Count of rows with invalid time data
- problematic_row_count: Count of rows with any quality issues
- clean_row_percentage: Percentage of rows without quality issues, rounded to two decimal places

Ensure the result is sorted by total_rows in descending order.

Write your request in the field below and click the "Check it!" button.

Use DuckDB syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

Get hint Copy code Clear editor

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.

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 nameTypeNULLKeyDefaultExtra
VendorIDINTEGERYES[null][null][null]
tpep_pickup_datetimeTIMESTAMPYES[null][null][null]
tpep_dropoff_datetimeTIMESTAMPYES[null][null][null]
passenger_countBIGINTYES[null][null][null]
trip_distanceDOUBLEYES[null][null][null]
RatecodeIDBIGINTYES[null][null][null]
store_and_fwd_flagVARCHARYES[null][null][null]
PULocationIDINTEGERYES[null][null][null]
DOLocationIDINTEGERYES[null][null][null]
payment_typeBIGINTYES[null][null][null]
fare_amountDOUBLEYES[null][null][null]
extraDOUBLEYES[null][null][null]
mta_taxDOUBLEYES[null][null][null]
tip_amountDOUBLEYES[null][null][null]
tolls_amountDOUBLEYES[null][null][null]
improvement_surchargeDOUBLEYES[null][null][null]
total_amountDOUBLEYES[null][null][null]
congestion_surchargeDOUBLEYES[null][null][null]
Airport_feeDOUBLEYES[null][null][null]