> Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6
> would solve this problem. Wouldn't I have to generate a series based on
> the date range (by day) and then group by DOW _after_ that? Can you give
> me an example of how I'd do it with a series based on 0 to 6?
Looks like David Johnston beat me to it! :-) But this is what I had in mind:
SELECT s.d AS dow,
COUNT(u.id) c
FROM generate_series(0, 6) s(d)
LEFT OUTER JOIN users u
ON EXTRACT(dow FROM created) = s.d
GROUP BY dow
ORDER BY dow
;
You can also get human-readable DOW names by creating a 7-row CTE table
and joining to it based on the numeric dow.
Paul
