I have a table set up like below, it only captures the total balance on that day:

 -------- ------------ -------- 
|   id   |    date    | wallet |
 ---- --------- ---------- -----
| 10056  | 2022-03-01 |  120   | 
| 10056  | 2022-03-02 |   40   |
| 10056  | 2022-03-04 |   80   |
| 10056  | 2022-03-08 |   23   |
| 13572  | 2022-03-01 |   20   |
| 13572  | 2022-03-03 |   17   |
 -------- ------------ -------- 

I wonder if it is possible to calculate only the spending? I can only think about using 'CASE' to stop calc the number that is small than yesterday, but I have no idea how to calculate the minus row by row. and this should be the result:

-- March Spending per user
 --------- --------- 
|   id    |  spend  |
 --------- --------- 
| 10056   | 103     |
| 13572   | 3       |
 --------- --------- 

I am not sure whether my SQL concept is correct or not.

CodePudding user response：

One option is to compute differences between consecutive wallet values, then exclude the negative ones (as they are wallet incomes instead of expenses) and sum up. Check the following query:

WITH cte AS (
    SELECT *, 
           wallet - LEAD(wallet) OVER(PARTITION BY id 
                                      ORDER     BY date)  AS diff
    FROM balances
)
SELECT id,
       SUM(diff)
FROM cte
WHERE diff > 0
GROUP BY id

Try it here.

CodePudding user response：

if I have well interpreter your issue you have to approch the problem in two phases:

1. to add to each row the wallet walue of the previous row so that you can apply the proper logic.
2. apply the logic that allow you to get the value of the field and group them.

assuming to generate your original table named as prova, my query suggestion could be:

select id, sum(case when prev_wallet>wallet then prev_wallet- wallet else 0 end) expense
from
(
select p.id, p.date, p.wallet, (select wallet from prova where id=p.id and date = (select max(date) date from prova where date<p.date and id=p.id)) prev_wallet
from prova p
order by p.id, p.date
) A
group by id 

Bye