PostgreSQL – Return First Record After Sum of Specific Value

postgresqlpostgresql-9.3

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)?

Best Answer

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

Related Question