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),
'1
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 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.
>>
>> O
On Mon, Jul 6, 2015 at 6:16 PM, Michael Nolan 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) for the D
On 7/6/15, Robert DiFalco 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 what I started wit
On Mon, Jul 6, 2015 at 4:40 PM, Robert DiFalco
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 forgot to cha
>
> 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 a
Please follow list conventions and either respond inline or bottom-post.
On Mon, Jul 6, 2015 at 3:30 PM, Robert DiFalco
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 h
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
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 J
On Mon, Jul 6, 2015 at 2:04 PM, Robert DiFalco
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, count_of_days) AS ( SE
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
> 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 so
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::DAT
13 matches
Mail list logo
