WITH total_rides AS(
WITH actives AS (
SELECT * FROM riders WHERE account_created_datetime>='2016-01-01 00:00:00' AND
account_created_datetime<='2017-01-01 00:00:00' AND active=TRUE)
SELECT actives.rider_id, rides.ride_datetime::date, count(rides.ride_id) FROM actives JOIN rides ON
actives.rider_id = rides.rider_id WHERE rides.is_complete=true GROUP BY actives.rider_id,
rides.ride_datetime::date)
SELECT rider_id AS "Rider ID", to_char(avg(count),'FM999999999.00') AS "Average Daily Rides (on days when rides are taken)"
FROM total_rides GROUP BY rider_id;
