Average New Users Per DOW

[Robert DiFalco <robert.difalco@xxxxxxxxx>]

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.

TIA

R.