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).
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;