Analyze trip logs

Query trip statistics

Before running any query, create an index on the Logstore. Queries return no results without an index.

The following queries use the tpep_pickup_time Unix timestamp field to group data by hour.

• When are rides most in demand?

  Count rides and passengers by hour to identify peak times when extra vehicles are needed.

   *| select count(1) as deals, sum(passenger_count) as passengers,    
   (tpep_pickup_time %(24*3600)/3600+8)%24 as time        
   group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24

  

  The chart shows two clear peaks during morning and evening commutes. Deploy additional vehicles in these windows to reduce wait times.

• Which hours see the longest trips?

  Average trip distance per hour reveals when drivers handle longer cross-town fares rather than short hops.

  *| select  avg(trip_distance)  as trip_distance, 
  (tpep_pickup_time %(24*3600)/3600+8)%24 as time         
  group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24

  

  Hours with high average distances indicate elevated demand for long-haul capacity. Dispatch more vehicles during those hours to absorb the load.

• When does traffic slow down the most?

  Two queries expose congestion patterns: average ride duration per hour, and average time per unit of distance per hour.

  *| select  avg(tpep_dropoff_time-tpep_pickup_time)/60  as driving_minutes, 
  (tpep_pickup_time %(24*3600)/3600+8)%24 as time  
  group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24   

  

  *| select  sum(tpep_dropoff_time-tpep_pickup_time)/sum(trip_distance)  as driving_minutes, 
  (tpep_pickup_time %(24*3600)/3600+8)%24 as time        
  group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24

  Certain hours show heavy congestion, requiring more vehicles on the road to meet demand.

• Which hours are most profitable for drivers?

  Average total fare per hour shows when drivers earn the most per trip.

  *| select  avg(total_amount)  as dollars, 
  (tpep_pickup_time %(24*3600)/3600+8)%24 as time 
  group by (tpep_pickup_time %(24*3600)/3600+8)%24 order by time limit 24

  

  Average fare peaks around 4:00 AM. Drivers targeting maximum earnings can focus on this window.

• How are fares distributed across price ranges?

  Grouping fares into price bands shows where most trips fall and helps inform pricing strategy.

  *| select case when total_amount < 1 then 'bill_0_1'  
  when total_amount < 10 then 'bill_1_10' 
  when total_amount < 20 then 'bill_10_20' 
  when total_amount < 30 then 'bill_20_30' 
  when total_amount < 40 then 'bill_30_40' 
  when total_amount < 50 then 'bill_40_50' 
  when total_amount < 100 then 'bill_50_100' 
  when total_amount < 1000 then 'bill_100_1000' 
  else 'bill_1000_'  end 
  as bill_level , count(1) as count group by 
  case when total_amount < 1 then 'bill_0_1'
  when total_amount < 10 then 'bill_1_10' 
  when total_amount < 20 then 'bill_10_20' 
  when total_amount < 30 then 'bill_20_30' 
  when total_amount < 40 then 'bill_30_40' 
  when total_amount < 50 then 'bill_40_50' 
  when total_amount < 100 then 'bill_50_100' 
  when total_amount < 1000 then 'bill_100_1000' 
  else 'bill_1000_'  end 
  order by count desc 

  Most fares fall between $1 and $20, reflecting a high volume of short to medium-length trips.