Re: Average New Users Per DOW

On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan <htfoot(at)gmail(dot)com> wrote:

> > 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
>

​I'm not seeing how this is at all useful.

As you said, the average function ignores the null introduced by the union
so the final answer with and without the union is the same.

No matter how you work a "generate_series(0,6)" based query it will never
be able to give a correct answer expect accidentally. Each actual missing
date contributes a ZERO to the numerator and a ONE to the denominator in
the final division that constitutes the mean-average. You must have those
dates.

In a series with four Mondays ( 4, 0, 0, 8 ) the average desired is 3, not
6 (or 4). There is no way to make the denominator (number of Mondays) 4
instead of 3 by using generate_series(0,6).

David J.