Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Find interesting Citi Bike stations
-- What are the stations in a variety of neighborhoods that have a lot of bikes, often run out and often get replenished?
with variability as (
select
borough,
hood,
station_name,
station_id,
max(available_bikes) as max_bikes,
sum(case when available_bikes = 0 then 1 else 0 end) as times_no_bikes,
sum(case when available_docks = 0 then 1 else 0 end) as times_replenished
from
availability
where
station_status = 'In Service'
group by
station_id, station_name, hood, borough
),
percentiles as (
select
*,
ntile(100) over (order by max_bikes asc) max_bikes_percentile,
ntile(100) over (order by times_no_bikes asc) no_bikes_percentile,
ntile(100) over (order by times_replenished asc) times_replenished_percentile
from
variability
order by times_no_bikes
),
ranks as (
select
*,
(max_bikes_percentile + no_bikes_percentile + times_replenished_percentile) as score,
rank() over (partition by hood order by (max_bikes_percentile + no_bikes_percentile + times_replenished_percentile) desc) as rank
from
percentiles
where
max_bikes_percentile > 40
and no_bikes_percentile > 50
and times_replenished_percentile > 50
),
ranked_by_hood as (
select
*
from
ranks
where
rank = 1
order by
score desc
)
select
a.*
from
availability as a
join
ranked_by_hood as rbh
on a.station_id = rbh.station_id;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.