Re: Average New Users Per DOW

"David G. Johnston" <david.g.johnston@xxxxxxxxx> · Mon, 6 Jul 2015 16:19:00 -0400

Please follow list conventions and either respond inline or bottom-post.

On Mon, Jul 6, 2015 at 3:30 PM, Robert DiFalco <robert.difalco@xxxxxxxxx> wrote:
Paul, I'm sure I'm missing something but it seems like your approach will not work. It's because the LEFT OUTER JOIN is on the numeric day of the week. So if you had this query going over weeks or months of data wouldn't you have the same issue with the days that had no new users not being factored into the AVG?  I ended up doing something like this, which seems to work pretty well.
WITH usersByDay AS (
	SELECT cDate, COUNT(*) AS total
	FROM (
		SELECT generate_series(
			{CALENDAR_INTERVAL.START}::DATE,
			{CALENDAR_INTERVAL.END}::DATE,
			interval '1 day')::DATE AS cDate
	) AS c
	LEFT OUTER JOIN users u ON u.created::DATE = c.cDate
	GROUP BY cDate),

I am fairly certain this does not give you the correct results.  Specifically, the minimum value for each cDate is going to be 1 since count(*) counts NULLs.  count(u) should probably work.

SELECT dt, count(uid), count(*)
FROM generate_series('2015-01-01'::date, '2015-01-05'::date, '1 day'::interval) gs (dt)
LEFT JOIN (VALUES ('2015-01-01'::date, 1), ('2015-01-01',2),('2015-01-02',3)) users (dt, uid)
USING (dt)
GROUP BY dt;

David J.