-- Average fare amount of rides with 2+ passengers by day
CREATE VIEW avg_fare_w_2_plus_passenger_by_day AS (
    SELECT date_trunc('day', pickup_datetime) as day, avg(fare_amount) as avg_fare
    FROM rides
    WHERE passenger_count > 1
    GROUP BY day ORDER BY day
);

-- Total number of rides by day for first 5 days
CREATE VIEW ride_count_by_day AS (
  SELECT date_trunc('day', pickup_datetime) as day, COUNT(*) FROM rides
    GROUP BY day ORDER BY day
);

-- Number of rides by 5 minute intervals
--   (using the TimescaleDB "time_bucket" function)
CREATE VIEW rides_in_5min_intervals AS (
SELECT time_bucket('5 minute', pickup_datetime) AS five_min_interval, count(*) as rides
  FROM rides
  GROUP BY five_min_interval ORDER BY five_min_interval
);