On 7/6/15, Robert DiFalco <robert.difalco@gmail.com> wrote:
> I'm not sure how to create a result where I get the average number of new
> users per day of the week. My issues are that days that did not have any
> new users will not be factored into the average, giving an overinflated
> result.
>
> This is what I started with:
>
> WITH userdays AS
> (SELECT u.created::DATE AS created,
> to_char(u.created,'Dy') AS d,
> COUNT(*) AS total
> FROM users u
> GROUP BY 1,2),
> userdays_avg AS
> (SELECT extract('dow'
> FROM created) AS nDay,
> d AS "Day",
> AVG(total) AS "New Users"
> FROM userdays
> GROUP BY 1,2
> ORDER BY 1)
> SELECT "Day", "New Users"
> FROM userdays_avg
> ORDER BY nDay;
>
>
> But you can see it wont give correct results since (for example) Monday's
> with no new users will not be counted in the average as 0.
One way to handle this is to union your query with one that has a
generate_series (0,6) for the DOW column and nulls for the other
columns, then treat both that and your original query as a subquery
and do your averages, since nulls are not included in either count()
or average() aggregates:
select dow, count(*), avg(some_column) from (
select extract ('dow' from some_date) as dow, some_number from some_table
union select generate_series(0,6) as dow, null as some_number) as x
group by 1 order by 1
--
Mike Nolan
nolan@tssi.com
