Longest Completed Trip in Each City

Return the longest completed trip by distance in each city. If several trips tie for the longest distance, return all of them.

Expected output

city, trip_id, distance_km, fare. Sorted by city, trip_id.

Row order is checked. Column names are not checked; numbers are compared to 2 decimals.

Dataset: Ride-share marketplace

Trip requests in four US cities from January to March 2026, with drivers, riders, fares and ratings.

drivers

One row per driver. A driver only serves their own city.

riders

One row per rider account.

trips

One row per trip request, whatever its outcome.

trip_id	INTEGER	primary key, increases with requested_at
rider_id	INTEGER
driver_id	INTEGER	NULL when no driver was assigned
city	TEXT
requested_at	TEXT	YYYY-MM-DD HH:MM:SS (UTC)
dropoff_at	TEXT	NULL unless completed
status	TEXT	completed, cancelled_rider, cancelled_driver or no_driver
distance_km	REAL	estimated route distance
fare	REAL	USD, NULL unless completed
rider_rating	INTEGER	1-5 stars given by the rider, NULL if not rated

Engine: SQLite. Dates are text (YYYY-MM-DD); use strftime(), date() and julianday() for date math.

city	trip_id	distance_km	fare
Austin	50280	24.87	86.9
Denver	50471	22.28	44.71
Miami	50255	24.9	85.2