-- Total trip counts per month (over 1 min)
SELECT
  (
  SELECT
    COUNT(*)
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60) AS july_trips,
  (
  SELECT
    COUNT(*)
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60) AS aug_trips,
  (
  SELECT
    COUNT(*)
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
  duration > 60) AS sept_trips

-- Trips counts per month by member (over 1 min)
SELECT
  'member' as member_casual,
  (
    SELECT COUNT(*)
    FROM `rock-collector-403822.divvy_rides.202307_trips`
    WHERE duration > 60 AND member_casual = 'member'
  ) AS july_trips,
  
  (
    SELECT COUNT(*)
    FROM `rock-collector-403822.divvy_rides.202308_trips`
    WHERE duration > 60 AND member_casual = 'member'
  ) AS aug_trips,

  (
    SELECT COUNT(*)
    FROM `rock-collector-403822.divvy_rides.202309_trips`
    WHERE duration > 60 AND member_casual = 'member'
  ) AS sept_trips

UNION ALL

SELECT
  'casual' as member_casual,
  (
    SELECT COUNT(*)
    FROM `rock-collector-403822.divvy_rides.202307_trips`
    WHERE duration > 60 AND member_casual = 'casual'
  ) AS july_trips,
  
  (
    SELECT COUNT(*)
    FROM `rock-collector-403822.divvy_rides.202308_trips`
    WHERE duration > 60 AND member_casual = 'casual'
  ) AS aug_trips,

  (
    SELECT COUNT(*)
    FROM `rock-collector-403822.divvy_rides.202309_trips`
    WHERE duration > 60 AND member_casual = 'casual'
  ) AS sept_trips;

-- Total trip duration by membership (over 1 min)
SELECT
  "member" as member_casual,
  (SELECT
    SUM(duration) AS total_duration
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS july_trips,
  (SELECT
    SUM(duration) AS total_duration
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS aug_trips,
  (SELECT
    SUM(duration) AS total_duration
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS sept_trips,

UNION ALL

SELECT
  "casual" as member_casual,
  (SELECT
    SUM(duration) AS total_duration
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS july_trips,
  (SELECT
    SUM(duration) AS total_duration
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS aug_trips,
  (SELECT
    SUM(duration) AS total_duration
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS sept_trips

-- Average trip duration (over 1 min)
SELECT
  'member' as member_casual,
  (
    SELECT AVG(duration)
    FROM `rock-collector-403822.divvy_rides.202307_trips`
    WHERE duration > 60 AND member_casual = 'member'
  ) AS july_avg,
  
  (
    SELECT AVG(duration)
    FROM `rock-collector-403822.divvy_rides.202308_trips`
    WHERE duration > 60 AND member_casual = 'member'
  ) AS aug_avg,

  (
    SELECT AVG(duration)
    FROM `rock-collector-403822.divvy_rides.202309_trips`
    WHERE duration > 60 AND member_casual = 'member'
  ) AS sept_avg

UNION ALL

SELECT
  'casual' as member_casual,
  (
    SELECT AVG(duration)
    FROM `rock-collector-403822.divvy_rides.202307_trips`
    WHERE duration > 60 AND member_casual = 'casual'
  ) AS july_avg,
  
  (
    SELECT AVG(duration)
    FROM `rock-collector-403822.divvy_rides.202308_trips`
    WHERE duration > 60 AND member_casual = 'casual'
  ) AS aug_avg,

  (
    SELECT AVG(duration)
    FROM `rock-collector-403822.divvy_rides.202309_trips`
    WHERE duration > 60 AND member_casual = 'casual'
  ) AS sept_avg;

-- Median trip duration (over 1 min)
SELECT
  "member" as member_casual,
  (SELECT
    bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS july_trips,
  (SELECT
    bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS aug_trips,
  (SELECT
    bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS sept_trips,

UNION ALL

SELECT
  "casual" as member_casual,
  (SELECT
    bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS july_trips,
  (SELECT
    bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS aug_trips,
  (SELECT
    bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS sept_trips

-- Median trip duration combined (over 1 min)
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`
  )

SELECT
  member_casual,
  bqutil.fn.median(ARRAY_AGG(duration)) AS median
FROM
  combined_data
WHERE
  duration > 60
GROUP BY
  member_casual

-- Max duration ride length by membership
SELECT
  "member" as member_casual,
  (SELECT
    MAX(duration) as max_duration
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS july_trips,
  (SELECT
    MAX(duration) as max_duration
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS aug_trips,
  (SELECT
    MAX(duration) as max_duration
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
    duration > 60
    AND member_casual = "member"
  GROUP BY
    member_casual) AS sept_trips,

UNION ALL

SELECT
  "casual" as member_casual,
  (SELECT
    MAX(duration) as max_duration
  FROM
    `rock-collector-403822.divvy_rides.202307_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS july_trips,
  (SELECT
    MAX(duration) as max_duration
  FROM
    `rock-collector-403822.divvy_rides.202308_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS aug_trips,
  (SELECT
    MAX(duration) as max_duration
  FROM
    `rock-collector-403822.divvy_rides.202309_trips`
  WHERE
    duration > 60
    AND member_casual = "casual"
  GROUP BY
    member_casual) AS sept_trips

-- average trip duration by member by day of week
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`)

SELECT day_of_week, member_casual, ROUND(AVG(duration),2) AS avg_trip_duration
FROM combined_data
WHERE duration > 60
GROUP BY day_of_week, member_casual

-- median trip duration by member by day of week
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`)

SELECT day_of_week, member_casual, bqutil.fn.median(ARRAY_AGG(duration)) AS median_duration
FROM combined_data
WHERE duration > 60
GROUP BY day_of_week, member_casual

-- Count trips between member and casual per day of week in Q3 (over 1 min)
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`)

SELECT member_casual, day_of_week, COUNT(day_of_week) AS num_rides
FROM combined_data
WHERE duration > 60
GROUP BY member_casual, day_of_week

-- Count weekday trips between member and casual winthin time blocks (over 1 min)
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`)

SELECT
  member_casual,
  COUNTIF(EXTRACT(HOUR FROM started_at) BETWEEN 23 AND 2) AS `23_to_2`,
  COUNTIF(EXTRACT(HOUR FROM started_at) BETWEEN 3 AND 6) AS `3_to_6`,
  COUNTIF(EXTRACT(HOUR FROM started_at) BETWEEN 7 AND 10) AS `7_to_10`,
  COUNTIF(EXTRACT(HOUR FROM started_at) BETWEEN 11 AND 14) AS `11_to_14`,
  COUNTIF(EXTRACT(HOUR FROM started_at) BETWEEN 15 AND 18) AS `15_to_18`,
  COUNTIF(EXTRACT(HOUR FROM started_at) BETWEEN 19 AND 22) AS `19_to_22`,
FROM combined_data
WHERE day_of_week IN ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
  AND duration > 60
GROUP BY member_casual;

-- Average weekday trips between member and casual winthin time blocks (over 1 min)
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`
  ),

trip_counts AS (
  SELECT
    member_casual,
    EXTRACT(WEEK FROM started_at) AS week,
    EXTRACT(DAY FROM started_at) AS day,
    EXTRACT(HOUR FROM started_at) AS hour,
    day_of_week AS weekday,
    COUNT(*) AS trip_count
  FROM combined_data
  WHERE day_of_week IN ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
  AND duration > 60
  GROUP BY member_casual, week, day, weekday, hour),

time_blocks AS (
  SELECT
    member_casual,
    week,
    day,
    weekday,
    SUM(CASE WHEN (hour >= 23) AND (hour <= 2) THEN trip_count ELSE 0 END) AS `23_to_2`,
    SUM(CASE WHEN hour BETWEEN 3 AND 6 THEN trip_count ELSE 0 END) AS `3_to_6`,
    SUM(CASE WHEN hour BETWEEN 7 AND 10 THEN trip_count ELSE 0 END) AS `7_to_10`,
    SUM(CASE WHEN hour BETWEEN 11 AND 14 THEN trip_count ELSE 0 END) AS `11_to_14`,
    SUM(CASE WHEN hour BETWEEN 15 AND 18 THEN trip_count ELSE 0 END) AS `15_to_18`,
    SUM(CASE WHEN hour BETWEEN 19 AND 22 THEN trip_count ELSE 0 END) AS `19_to_22`,
  FROM trip_counts
  GROUP BY member_casual, week, day, weekday)

SELECT
  member_casual,
  ROUND(AVG(`23_to_2`),2) AS `avg_23_to_2`,
  ROUND(AVG(`3_to_6`),2) AS `avg_3_to_6`,
  ROUND(AVG(`7_to_10`),2) AS `avg_7_to_10`,
  ROUND(AVG(`11_to_14`),2) AS `avg_11_to_14`,
  ROUND(AVG(`15_to_18`),2) AS `avg_15_to_18`,
  ROUND(AVG(`19_to_22`),2) AS `avg_19_to_22`
FROM
  time_blocks
GROUP BY
  member_casual

-- Average count trips per stations per day member riders
WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`
  ),

station_rides_per_day AS (
  SELECT
    EXTRACT(day FROM started_at) AS day,
    EXTRACT(dayofweek FROM started_at) AS day_of_week,
    start_station_name,
    start_station_id,
    COUNT(ride_id) AS count_rides
  FROM
    combined_data
  WHERE
    duration >= 60 AND member_casual = 'casual' AND start_station_id IS NOT NULL
  GROUP BY
    day,
    day_of_week,
    start_station_name,
    start_station_id
)

SELECT
  start_station_name,
  start_station_id,
  ROUND(AVG(count_rides)) AS avg_trips_day
FROM station_rides_per_day
GROUP BY
  start_station_name,
  start_station_id
ORDER BY
  avg_trips_day DESC
LIMIT 10

-- Count of rides by rideable_type and member status

WITH combined_data AS (
  SELECT * FROM `rock-collector-403822.divvy_rides.202307_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202308_trips`
  UNION ALL
  SELECT * FROM `rock-collector-403822.divvy_rides.202309_trips`
  )

SELECT
  member_casual,
  rideable_type,
  COUNT(rideable_type) AS ride_count
FROM
  combined_data
WHERE
  duration >= 60
GROUP BY
  member_casual,
  rideable_type