Average New Users Per DOW

From: Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Average New Users Per DOW
Date: 2015-07-06 17:41:10
Message-ID: CAAXGW-zb3qqOz0+pkRP7_pedbK=JoxDqaVu+E+ytGU6jYur4gQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2015-07-06 17:42:14 Re: PgBouncer error - psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.6432"?
Previous Message Igor Neyman 2015-07-06 17:38:55 Re: PgBouncer error - psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.6432"?