CODEWITHSUNDEEP
postgresql
William Falcon
William Falcon

Reputation: 9813

Postgres cumulative sum over earlier months

I have a table that has % of users for that month. I want to add those percents to get a cumulative sum up to that month. I don't think my cumulative function is right.

So, row 5 should show cum_amt 0.1381...
row 4, 0.1381 + 0.010169 = 0.148,
etc...

But row 1 should be 0.164 bc it is not bayside

enter image description here

select 
    created, name, new_users, possible, 
    (new_users::FLOAT / possible) as penetration,
    sum((new_users::FLOAT / possible)) OVER (ORDER BY created) AS cum_amt

from 
    mom_users

where 
    new_users > 10

order by
    name, created
    desc

Upvotes: 0

Views: 86

Answers (2)

JKim
JKim

Reputation: 155

I think this code works for you.

CREATE TABLE mom_users (
created timestamp,
name text,
new_users int,
possible int
);

insert into mom_users values('2016-11-01 00:00:00', 'banning', 11, 67);
insert into mom_users values('2016-10-01 00:00:00', 'bayside', 253, 1180);
insert into mom_users values('2016-09-01 00:00:00', 'bayside', 92, 1180);
insert into mom_users values('2016-06-01 00:00:00', 'bayside', 12, 1180);
insert into mom_users values('2016-05-01 00:00:00', 'bayside', 163, 1180);

SELECT *, new_users/possible::real penetration, sum(new_users/possible::real) OVER (PARTITION BY name ORDER BY CREATED) cum_amt 
FROM mom_users 
WHERE new_users > 10
ORDER BY created DESC;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I think you want a partition by and a division after the analytic function:

select created, name, new_users, possible, 
       (new_users::FLOAT / possible) as penetration,
       (sum(new_users::FLOAT) over (partition by name order by created) /
        sum(possible::FLOAT) over (partition by name order by created)
       ) as cum_amt

from mom_users
where new_users > 10
order byname, created desc;

Upvotes: 1

Related Questions