Bike Share Data

select count(distinct(bikeid)) bikes from rides;

select count(distinct(id)) stations from stations;

select usertype, count(*) [count] from rides group by usertype;

select round(avg(numrides)) [average rides]
from (
    select bikeid, count(*) numrides
      from rides
  group by bikeid);

create temporary table idletime (
    bikeid,
    seconds
);

insert into idletime
select bikeid,
       strftime('%s', starttime) - strftime('%s', lag(endtime) over
                                                  (partition by bikeid
                                                       order by endtime))
  from rides;

select (avg(seconds)/60)/60 hours
  from (select seconds
          from idletime
      order by seconds
         limit 2 - (select count(*) from idletime) % 2    -- odd 1, even 2
        offset (select (count(*) - 1) / 2
                  from idletime))

with [per bike distance] as (
  select bikeid, starttime, pd.distance
    from rides r
    join pairdistance pd on r.from_id=pd.from_id and r.to_id=pd.to_id 
)
  select bikeid, round(sum(distance)) total
    from [per bike distance] 
group by bikeid 
order by total desc
limit 10;

  select name, count(*) circuits 
    from rides 
    join stations on from_id=stations.id 
   where from_id = to_id 
group by from_id 
order by circuits desc 
limit 10;

  select stations.name, count(*) [count]
    from stations 
    join rides on rides.from_id=stations.id or rides.to_id=stations.id 
group by stations.id 
order by [count] asc
   limit 10;

  select from_id [departing station], strftime('%w', starttime) day, count(*) [count]
    from rides
group by [departing station], day
order by [departing station], day
   limit 14;

select usertype, s1.name [from], s2.name [to], (duration/60) minutes, distance
  from rides r 
  join pairdistance pd on r.from_id=pd.from_id and r.to_id=pd.to_id 
  join stations s1 on r.from_id=s1.id
  join stations s2 on r.to_id=s2.id
 where r.from_id <> r.to_id
   and distance < 300
   and duration > 600
 limit 10;