[GENERAL] Average New Users Per DOW

[Robert DiFalco]

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.