Unable to get the balance sheet for the particular group of users. how can i be doing it

user_id  created_at       status       amount
_____________________________________________
10196   31-08-2019 15:38  billcreated  21900
10196   05-09-2019 05:40  billpaid     43800
10196   15-12-2019 15:54  billcreated  11200
10205   15-03-2019 16:02  billcreated  2700
10205   31-03-2019 16:02  billcreated  27000
10205   23-04-2019 09:54  billpaid     3000

to get this table i used the query as below

select id,user_id,created_at,status,amount
from settlementdata
order by user_id,STR_TO_DATE(created_at, '%d-%m-%Y %H:%i');

but now I want to get the table as below

user_id  created_at       status       amount  balance
______________________________________________________
10196   31-08-2019 15:38  billcreated  21900   21900 (as no previous records exits)
10196   05-09-2019 05:40  billpaid     43800   -21900 (balance - paid amount i.e 21900-43800)
10196   15-12-2019 15:54  billcreated  11200   -10700 (balance + bill created amount i.e -21900+11200)
10205   15-03-2019 16:02  billcreated  2700    2700 (as no previous records exits)
10205   31-03-2019 16:02  billcreated  27000   29700 (as 2700+27000) 
10205   23-04-2019 09:54  billpaid     3000    26700 (as 29700-3000)

for this i used a query as below

select id, user_id,created_at,status, amount, sum(
    case user_id
        when lag(user_id) over(order by id) = user_id and status = "billcreated"
        then amount
        when lag(user_id) over(order by id) = user_id and status = "billpaid"
        then amount*(-1)
        else 0
        end ) from (select id,user_id,created_at,status,amount from settlementdata
                    order by user_id,STR_TO_DATE(created_at, '%d-%m-%Y %H:%i')
                    limit ) as T;

but still I am unable to get the resulted tab rather I got an error message as UNKNOWN_CODE_PLEASE_REPORT: You cannot use the window function ‘lag’ in this context.

Any help that i can get for the same is highly appreciated.

Thanks in advance

Answer

SELECT *, SUM(CASE WHEN status = 'billcreated' 
                   THEN amount 
                   ELSE -amount 
                   END) OVER (PARTITION BY user_id 
                              ORDER BY STR_TO_DATE(created_at, '%d-%m-%Y %H:%i') ASC) balance
FROM settlementdata

Advertisement

Answer