Average New Users Per DOW - Mailing list pgsql-general

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;