South Bronx analysis

south_bronx.sql

WITH weekly_stats AS (
  SELECT
    date_trunc('week', pickup_datetime)::date AS week,
    round(sum(trip_miles) / sum(trip_minutes) * 60, 2) AS mean_mph,
    round(median(mph), 2) AS median_mph,
    round(avg(trip_miles), 2) AS avg_miles,
    count(*) AS trips,
    count(DISTINCT pickup_datetime::date) AS days_counted
  FROM fhv_trips_expanded
  WHERE reasonable_time_distance_fare = 1
    AND solo_non_special_request = 1
    AND pickup_location_id = 168
    AND dropoff_location_id = 168
    AND pickup_datetime >= '2024-01-01'
    AND company = 'uber'
    AND (pickup_datetime - on_scene_datetime)::int BETWEEN 0 AND 119
    AND toDayOfWeek(pickup_datetime) IN (1, 2, 3, 4, 5)
    AND toHour(pickup_datetime) BETWEEN 8 AND 17
    AND trip_miles < 10
  GROUP BY 1
)
SELECT
  *,
  toYear(week) AS year,
  toWeek(week, 1) AS week_number
FROM weekly_stats
WHERE days_counted = 5
ORDER BY week
FORMAT CSVWithNames;