Re: Average New Users Per DOW - Mailing list pgsql-general

> 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