kevinburke / gobike

https://bikeshare.science
6 stars 0 forks source link

Load trip data into Postgres #2

Closed kyleconroy closed 6 years ago

kyleconroy commented 6 years ago

Some cool queries against this data

Number of trips per day

select
  date_trunc('day', lower(during)),
  count(*),
  count(distinct bike)
from trips t
join stations s on t.start_station = s.id
where s.city = 'San Francisco'
and date_trunc('year', lower(during)) = '2018-01-01'
group by 1
order by 1

Most Popular day of the week

select
  EXTRACT(DOW from lower(during)),
  count(*)
from trips t
group by 1
order by 1

Trip breakdown by generation

select
  width_bucket(member_birth_year, 1910, 2020, 12),
  count(*)
from trips
where member_birth_year IS NOT NULL
group by 1
order by 1;

Trips that span cities

select count(*)
from trips t 
join stations s on t.start_station = s.id 
join stations x on t.end_station = x.id 
where x.city != s.city;