Re: Average New Users Per DOW

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

On Mon, Jul 6, 2015 at 3:30 PM, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com>
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.
​