NYC Taxi Data
Analytics Dashboards

Powered byQuestDB andGrafana

A real-time replay of 146,393,317 taxi rides, carrying 238,016,495 passengers across New York City in 2016.

Taxis in transit

An estimate of how many taxis were carrying passengers at this time, eight years ago today.

SELECT
count()
FROM
trips
WHERE
pickup_datetime
BETWEEN
dateadd('h',-2,dateadd('y',-8,now()))
AND
dateadd('y',-8,now())
AND dropoff_datetime > dateadd('y',-8,now())
SAMPLE BY 2h
Try it online

Passengers in transit

An estimate of how many passengers were in transit by taxi at this time eight years ago.

select sum(passenger_count) from trips
WHERE
pickup_datetime
BETWEEN
dateadd('h',-2,dateadd('y',-8,now()))
AND
dateadd('y',-8,now())
AND dropoff_datetime > dateadd('y',-8,now())
SAMPLE BY 2h
Try it online

Total fare today

Total fare accumulated since midnight on this day eight years ago.

SELECT
sum(fare_amount) revenue
FROM
trips
WHERE
pickup_datetime BETWEEN
dateadd('y', -8, timestamp_floor('d', now()))
AND dateadd('y', -8, now())
Try it online

Trips today

Total taxi fares on this calendar day eight years ago, from midnight to the current time.

SELECT
count() trips
FROM
trips
WHERE
pickup_datetime BETWEEN
dateadd('y', -8, timestamp_floor('d', now()))
AND dateadd('y', -8, now())
Try it online

Geodata

Generate deep business insights by combining locations and timestamps.

Dropoffs (last 2 min)

A heatmap of taxi dropoffs in New York City in the past two minutes, eight years ago today.

SELECT
dropoff_datetime,
dropoff_latitude,
dropoff_longitude
FROM
trips
WHERE
pickup_datetime BETWEEN
dateadd('h', -4, dateadd('y', -8, now()))
AND dateadd('y', -8, now())
AND dropoff_datetime BETWEEN
dateadd('m', -2, dateadd('y', -8, now()))
AND dateadd('y', -8, now())
Try it online

Pickups (last 2 min)

A heatmap of taxi pickups in New York City in the last two minutes, eight years ago today.

SELECT
pickup_datetime,
pickup_latitude,
pickup_longitude
FROM
trips
WHERE
pickup_datetime BETWEEN
dateadd('m', -2, dateadd('y', -8, now()))
AND dateadd('y', -8, now())
Try it online

Insights

Discover trends in your data using modern SQL analytics.

Tip / fare correlation

A scatterplot correlating tips to total fare of a NYC taxi trip. Points in purple indicate those correlating to fixed percentages of the total fare -- 10%, 15%, 20%, etc., with a 1% tolerance.

SELECT
tip_amount,
fare_amount,
(
abs(tip_amount / fare_amount - 0.1) < 0.01
OR abs(tip_amount / fare_amount -0.15) < 0.01
OR abs(tip_amount / fare_amount - 0.2) < 0.01
OR abs(tip_amount / fare_amount - 0.3) < 0.01
) percent_tip
FROM
trips
WHERE
pickup_datetime BETWEEN
dateadd('m', -30, dateadd('y', -8, now()))
AND dateadd('y', -8, now())
AND fare_amount < 70
AND tip_amount < 15
AND tip_amount > 0;
Try it online

Fare average

Utilising window functions to compute average taxi fares, since midnight (UTC) of today's calendar day and now (relative to eight years ago).

WITH x AS (
SELECT
pickup_datetime,
avg(fare_amount) OVER (
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) avgFare,
avg(fare_amount) OVER (
ORDER BY
pickup_datetime RANGE BETWEEN 10 MINUTE PRECEDING
AND CURRENT ROW
) avgFareHourlyMavg
FROM trips
WHERE
pickup_datetime BETWEEN
dateadd('y', -8, timestamp_floor('d', now()))
AND dateadd('y', -8, now())
)
SELECT
x.pickup_datetime,
last(avgFare) average_fare_since_midnight,
last(avgFareHourlyMavg) average_fare_last_last_hour
FROM x
SAMPLE BY 1m
Try it online

Build your own on QuestDB

High performance ingest & slick visualizations.
Perfect for analytics.


Download QuestDB