PostgreSQL, select rows until certain amount is reached and exceeded

Question

I have a table with id,date and amount, i need to get the list,of id's, ordered by date DESC, that in total to be greater or equal than some specific value.

 id |   date   | amount 
----|----------|--------
 1  | 1/1/2017 |   3 
 2  | 2/1/2017 |   5 
 3  | 3/1/2017 |   4
 4  | 4/1/2017 |   2 
 5  | 5/1/2017 |   7 
 6  | 6/1/2017 |   4

For example

1. I need to get all rows that in total will be >= 12, ORDER BY date DESC. Then it's going to be 3 rows: 6, 5, 4, as 4+7+2 = 13.
2. I need to get all rows that in total will be >= 5, ORDER BY date DESC. Then it's going to be 2 rows: 6, 5 as 4+7 = 11.
3. I need to get all rows that in total will be >= 15, ORDER BY date DESC. Then it's going to be 4 rows: 6, 5, 4, 3 as 4+7+2+4 = 17.

There is a very similar question and solution Postgresql select until certain total amount is reached but I need a bit different.

Thanks for any help.

Answer 1

Just use a cumulative sum:

select t.*
from (select t.*, sum(amount) over (order by date desc) as running_amount
      from t
     ) t
where running_amount - amount < 12
order by date desc;