1

I need to create a query to sum values until I reach a certain amount.

select client_id, dt, SUM(value)
from my_table
group by 1, 2;

My table is something like this:

client_id |    dt      |  value
----------+------------+-------
    23    | 2023-01-01 |   200
    23    | 2023-01-02 |   800
    23    | 2023-01-03 |   500

My expected result is client_id: 23 | sum: 2023-01-02, that is the day that the sum reached 1000 (200 + 800).

devinho
  • 404
  • 4
  • 18

1 Answers1

2

Combine a windowed sum() with distinct on as follows:

with running as (
  select client_id, dt, 
         sum(value) over (partition by client_id 
                              order by dt) as run_total
    from my_table
)
select distinct on (client_id) client_id, dt, run_total
  from running
 where run_total >= 1000
 order by client_id, dt;

Working example.

Mike Organek
  • 11,647
  • 3
  • 11
  • 26