How to calculate the minimum amount during the period?

Question

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?

Does this answer your question? [How to get cumulative sum](https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum) — Ken Y-N, May 27 '22 at 06:50
Calculate cumulative sum in CTE or subquery and select the row with minimal value in outer query (ORDER BY LIMIT 1). PS. What if 2 or more rows have the same minimal balance? — Akina, May 27 '22 at 06:50
In that case, selecting the first row, or the last one doesn't matter for me @Akina — Martin AJ, May 27 '22 at 06:51
Ok. If so then simple `ORDER BY cumulative_sum ASC, created_at DESC LIMIT 1` - this will produce deterministic output. — Akina, May 27 '22 at 06:55
well, how does `cumulative_sum` look in the `SELECT`? @Akina — Martin AJ, May 27 '22 at 06:55

score 1 · Answer 1 · answered May 27 '22 at 07:20

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

score 0 · Accepted Answer · answered May 27 '22 at 07:02

0

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;

answered May 27 '22 at 07:02

Akina

39,301
5
14
25

Thank you so much, just out of curiosity, what does `rn` stand for? – Martin AJ May 27 '22 at 07:02
@MartinAJ The rows for definite user are sorted/enumerated by cumulative sum and date, and `rn=1` matches the most recent row from ones with minimal cumulative sum (current balance value). – Akina May 27 '22 at 07:05

How to calculate the minimum amount during the period?

2 Answers2