Paul, I'm sure I'm missing something but it seems like your approach will
not work. It's because the LEFT OUTER JOIN is on the numeric day of the
week. So if you had this query going over weeks or months of data wouldn't
you have the same issue with the days that had no new users not being
factored into the AVG? I ended up doing something like this, which seems
to work pretty well.
WITH usersByDay AS (
SELECT cDate, COUNT(*) AS total
FROM (
SELECT generate_series(
{CALENDAR_INTERVAL.START}::DATE,
{CALENDAR_INTERVAL.END}::DATE,
interval '1 day')::DATE AS cDate
) AS c
LEFT OUTER JOIN users u ON u.created::DATE = c.cDate
GROUP BY cDate),
avgUsersByDOW AS (
SELECT extract('dow' FROM cDate) AS nDay,
to_char(cDate,'Dy') AS "Day",
ROUND(AVG(total), 2) AS "New Users"
FROM usersByDay
GROUP BY 1, 2
ORDER BY 1)
SELECT "Day", "New Users" FROM avgUsersByDOW ORDER BY nDay
On Mon, Jul 6, 2015 at 11:30 AM, Paul Jungwirth <p...@illuminatedcomputing.com
> wrote:
> 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
>
>
