Обсуждение: Average New Users Per DOW

 > 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

This is a pretty common problem with time-series queries when there is
sparse data. My go-to solution is to use generate_series---in your case
from 0 to 6---then do a left join from there to your actual data.

Paul

> 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

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