2

I have a table where I want to get the first record after a certain value is reached with sum. I'm using PostgreSQL 9.3.

So - for the following table, summing the amount column for a specific user (eg user 1) I want the record where the amount gets to zero or below, in this case record with deposit_id 8.

deposit_id |   user_id            |   amount
-----------------------------------------------------------
1          |   1                  |   +10
2          |   1                  |   +5
3          |   2                  |   +10
4          |   1                  |   +5
5          |   2                  |   +5
6          |   1                  |   -15
7          |   1                  |   -3
8          |   1                  |   -3
9          |   1                  |   -5

What would the best way to structure a query for a specific user to sum the values and stop when the value has reached 0 or below and return the record which pushed it over the edge (the first record where total sum of amounts <= 0)?

1

A cumulative sum can be reached with a Window function and a CTE, the difficult part is to stop it.

sum(amount) over (partition by user_id order by deposit_id)
create table foo(deposit_id int,user_id int,amount int);
insert into foo values
(1,1,10),
(2,1,5),
(3,1,5),
(4,1,-15),
(5,1,-3),
(6,1,-3),
(7,1,-5);
with CumSum as
(
    select deposit_id, user_id, amount,
           sum(amount) over (partition by user_id order by deposit_id) as total
    from foo
)
select deposit_id, user_id, amount, total
from   CumSum
order by user_id, total desc;
deposit_id | user_id | amount | total
---------: | ------: | -----: | ----:
         3 |       1 |      5 |    20
         2 |       1 |      5 |    15
         1 |       1 |     10 |    10
         4 |       1 |    -15 |     5
         5 |       1 |     -3 |     2
         6 |       1 |     -3 |    -1
         7 |       1 |     -5 |    -6

To get the first element where total <= 0

with CumSum as
(
    select deposit_id, user_id, amount,
           sum(amount) over (partition by user_id order by deposit_id) as total
    from foo
)
select   deposit_id, user_id, amount, total
from     CumSum
where    total <= 0
order by user_id, total desc
limit 1;
deposit_id | user_id | amount | total
---------: | ------: | -----: | ----:
         6 |       1 |     -3 |    -1

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.