# NYC yellow taxi trips, January 2025, read straight from the TLC's public
# Parquet file over HTTPS. Walkthrough: https://ducklocal.app/docs/example-nyc-taxi

query "hourly" {
  sql = <<SQL
    SELECT date_trunc('hour', tpep_pickup_datetime) AS hour, count(*) AS trips
    FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
    WHERE tpep_pickup_datetime >= '2025-01-01'
      AND tpep_pickup_datetime <  '2025-02-01'
    GROUP BY 1
    ORDER BY 1
  SQL
}

query "by_hour_of_day" {
  sql = <<SQL
    SELECT format('{:02d}:00', hour(tpep_pickup_datetime)) AS pickup_hour,
           count(*) AS trips
    FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
    GROUP BY 1
    ORDER BY 1
  SQL
}

query "by_payment" {
  sql = <<SQL
    SELECT CASE payment_type
             WHEN 1 THEN 'Credit card' WHEN 2 THEN 'Cash'
             WHEN 3 THEN 'No charge'   WHEN 4 THEN 'Dispute'
             ELSE 'Other'
           END AS payment,
           count(*) AS trips,
           round(100 * avg(tip_amount / fare_amount), 1) AS tip_pct
    FROM 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet'
    WHERE fare_amount > 0
    GROUP BY 1
    ORDER BY trips DESC
  SQL
}

plot "trips_per_hour" {
  type  = "area"
  query = query.hourly
  x     = hour
  y     = trips
  title = "Trips per hour, January 2025"
}

plot "hour_of_day" {
  type  = "bar"
  query = query.by_hour_of_day
  x     = pickup_hour
  y     = trips
  title = "Trips by hour of day"
}

plot "tips" {
  type  = "bar"
  query = query.by_payment
  x     = payment
  y     = tip_pct
  title = "Average tip, % of fare, by payment type"
}

plot "payment_table" {
  type  = "table"
  query = query.by_payment
  x     = payment
  title = "Trips and tips by payment type"
}