NYC Yellow Taxi Demand & Tipping Patterns

WITH clean AS (
  SELECT *
  FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2023`
  WHERE fare_amount > 0
    AND trip_distance > 0
    AND dropoff_datetime > pickup_datetime
    AND trip_distance / (
      TIMESTAMP_DIFF(dropoff_datetime,
                     pickup_datetime, SECOND) / 3600.0
    ) < 80
)
SELECT
  FORMAT_TIMESTAMP('%A', pickup_datetime)      AS weekday,
  EXTRACT(HOUR FROM pickup_datetime)           AS pickup_hour,
  COUNT(*)                                     AS trips,
  ROUND(AVG(fare_amount), 2)                   AS avg_fare,
  ROUND(AVG(trip_distance /
    (TIMESTAMP_DIFF(dropoff_datetime,
       pickup_datetime, SECOND) / 3600.0)), 1) AS avg_mph
FROM clean
GROUP BY weekday, pickup_hour
ORDER BY trips DESC;

SELECT
  CASE payment_type
    WHEN 1 THEN 'Credit card'
    WHEN 2 THEN 'Cash'
    ELSE 'Other'
  END                                          AS payment,
  COUNT(*)                                     AS trips,
  ROUND(AVG(tip_amount), 2)                    AS avg_tip,
  ROUND(100 * AVG(tip_amount /
        NULLIF(fare_amount, 0)), 1)            AS avg_tip_pct,
  ROUND(100 * COUNTIF(tip_amount = 0)
        / COUNT(*), 1)                         AS pct_zero_tip
FROM clean
GROUP BY payment
ORDER BY trips DESC;

-- Blended average without this split understates real
-- tipping by ~35%: cash tips exist but are never recorded.