-- 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