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.
Yes you are right, I forgot to change COUNT(*) to COUNT(id), as you
mention COUNT(u.*) will also
Please follow list conventions and either respond inline or bottom-post.
On Mon, Jul 6, 2015 at 3:30 PM, Robert DiFalco robert.difa...@gmail.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
On Mon, Jul 6, 2015 at 4:40 PM, Robert DiFalco robert.difa...@gmail.com
wrote:
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.
Yes you are right, I
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
This is a pretty common problem with time-series queries when there is
sparse data. My go-to
Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6
would solve this problem. Wouldn't I have to generate a series based on the
date range (by day) and then group by DOW _after_ that? Can you give me an
example of how I'd do it with a series based on 0 to 6?
On Mon, Jul 6, 2015
On Mon, Jul 6, 2015 at 2:04 PM, Robert DiFalco robert.difa...@gmail.com
wrote:
Wouldn't I have to generate a series based on the date range (by day) and
then group by DOW _after_ that?
You are correct.
WITH userdays (dow, user_count) AS ( existing_query, more or less )
, day_counts (dow,
Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6
would solve this problem. Wouldn't I have to generate a series based on
the date range (by day) and then group by DOW _after_ that? Can you give
me an example of how I'd do it with a series based on 0 to 6?
Looks like David
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
On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan htf...@gmail.com wrote:
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.
One way to handle this is to union your query with one that has a
generate_series (0,6)
On 7/6/15, Robert DiFalco robert.difa...@gmail.com wrote:
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
On Mon, Jul 6, 2015 at 5:50 PM, David G. Johnston
david.g.johns...@gmail.com wrote:
On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan htf...@gmail.com wrote:
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.
Here's a minor refinement that doesn't require knowing the range of dates
in the users table:
(select created, created as created2, count(*) as total from users
group by 1, 2
union
(select generate_series(
(select min(created)::timestamp from users),
(select max(created)::timestamp from users),
12 matches
Mail list logo
