weeks-grouping.sql

WITH weeks AS (
  SELECT distinct date_trunc('week', dates)::date AS start,
  daterange(date_trunc('week', dates)::date, (date_trunc('week', dates) + interval '7 days')::date) week
  FROM generate_series(date '2013-01-01', now(), '1 day')
  dates ORDER BY 1
)
SELECT weeks.start,
    count(whatever.*) as count
  FROM whatever
  JOIN weeks
    ON weeks.week @> whatever.created_at::date
  GROUP BY 1;