Get average number of rows per (day, hour)

gistfile1.txt

    SELECT avg(hits) AS value,
           extract(dow from ts) as day,
           extract(hour from ts) as hour
    FROM (
      WITH filled_dates as (
        select hour, 0 as blank_count from
          generate_series(
            (SELECT date_trunc('hour', MIN(timestamp)) FROM activities WHERE user_subject_id=#{user_id}),
            (SELECT MAX(timestamp) from activities WHERE user_subject_id=#{user_id}),
            '1 hour'
          ) as hour 
      ),
      counts as (
        SELECT count(timestamp) as hits,
               date_trunc('hour', timestamp) AS hour
        FROM activities
        WHERE user_subject_id=#{user_id}
        GROUP BY date_trunc('hour', timestamp)
      )
      select filled_dates.hour as ts, 
             coalesce(counts.hits, filled_dates.blank_count) as hits
        from filled_dates
          left outer join counts on counts.hour = filled_dates.hour
        order by filled_dates.hour
    ) AS sub1
    GROUP BY extract(dow from ts), extract(hour from ts)
    ORDER BY extract(dow from ts), extract(hour from ts)