How to get the time the wallet has reached to specific amount?-CodePudding

Assume the following table:

// Wallets
 ---- --------- -------- ---------------- 
| id | user_id | amount |   created_at   |
 ---- --------- -------- ---------------- 
| 1  | 200     | 1000   | 2022-01-01     |
| 2  | 200     | 2000   | 2022-01-01     |
| 3  | 200     | 1500   | 2022-01-02     |
| 4  | 200     | 5000   | 2022-01-03     | -- here
| 5  | 200     | 1000   | 2022-01-03     |   
| 6  | 200     | 3000   | 2022-01-04     |
 ---- --------- -------- ----------------

Also, the following query calculates the sum of the user's wallet:

select sum(amount) from wallets where user_id = 200

My question: I need to select max(created_at) when the amount has reached 9000. So the expected result is 2022-01-03.

Because 1000 2000 1500 5000 = 9500 and I need to get the time the last transaction happened to reach 9000, which is 5000 (#4) happened at 2022-01-03.

How can I get it?

Here is my query which is wrong:

select max(created_at)
from wallets
where user_id = 200
having sum(amount) >= 9000

CodePudding user response：

You have to get the cumulative sum line by line then find the lowest date where the amount is 9000 or higher

select min(id) id_reached, min(created_at) date_reached from (
  select id, created_at, sum(amount) over(order by id) sum from wallets where user_id = 200
) t1 where sum >= 9000

db<>fiddle