Search Postgresql Archives

Re: Average New Users Per DOW

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux