How to calculate the minimum amount during the period?-CodePudding

I have a table named wallet, which is like this:

// wallet
 ---- ---------- ---------- ------------ 
| id | user_id  |  amount  | created_at |
 ---- ---------- ---------- ------------ 
| 1  | 5        | 1000     | 2022-05-20 | -- 1000
| 2  | 5        | 500      | 2022-05-20 | -- 1500
| 3  | 5        | -1000    | 2022-05-21 | -- 500           <-- this
| 4  | 5        | 4000     | 2022-05-23 | -- 4500
| 5  | 5        | -2000    | 2022-05-23 | -- 2500
| 6  | 5        | 1000     | 2022-05-24 | -- 3500
 ---- ---------- ---------- ------------

As you can see, (after all deposits and withdrawals), sum(amount) is 500 at the lower point (minimum calculated amount) in the period which is happened at 2022-05-21. So, selecting this row is the expected result:

| 3  | 5        | -1000    | 2022-05-21 |

Any idea how can I get that?

CodePudding user response：

select t0.id, t0.user_id, t0.sum_amt, t0.rank_amt
from
(
    select t.id, t.user_id, sum_amt, rank() over(partition by t.user_id order by t.sum_amt) rank_amt
    from
    (
        select t1.id, t1.user_id, SUM(t2.amount) as sum_amt
        from wallet t1
            inner join wallet t2 on t1.id >= t2.id and t1.user_id = t2.user_id
        group by t1.id, t1.user_id
    ) t
) t0
where t0.rank_amt = 1;

Fiddle

CodePudding user response：

WITH
cte1 AS ( SELECT *, 
                SUM(amount) OVER (PARTITION BY user_id 
                                  ORDER BY created_at ASC) cumulative_sum 
         FROM wallet 
),
cte2 AS ( SELECT *,
                 ROW_NUMBER() OVER (PARTITION BY user_id 
                                    ORDER BY cumulative_sum ASC, 
                                             created_at DESC) rn
          FROM cte1
)
SELECT *
FROM cte2
WHERE rn = 1;