select 1 as id, 1 as transaction_typeid, 'bitcoin-credit' as transaction_type, 30 as amount, 30 as totalcoins from dual union all

select 2 as id, 2 as transaction_typeid, 'etherium-credit' as transaction_type, 20 as amount, 50 as totalcoins from dual union all

select 3 as id, 3 as transaction_typeid, 'bitcoin-debit' as transaction_type, -10 as amount, 40 as totalcoins from dual union all

select 4 as id, 4 as transaction_typeid, 'etherium-debit' as transaction_type, -5 as amount, 35 as totalcoins from dual union all

select 5 as id, 1 as transaction_typeid, 'bitcoin-credit' as transaction_type, 15 as amount, 50 as totalcoins from dual union all

select 6 as id, 2 as transaction_typeid, 'etherium-credit' as transaction_type, 10 as amount, 60 as totalcoins from dual union all

select 7 as id, 4 as transaction_typeid, 'etherium-debit' as transaction_type, -5 as amount, 55 as totalcoins from dual

AS (SELECT a.*,

CASE

WHEN ( transaction_typeid = 1

OR transaction_typeid = 3 ) THEN 0

ELSE 1

END AS category

FROM cryptotransactionledger a

ORDER BY id),

cte2

AS (SELECT CASE

WHEN ( category = 0 ) THEN 'bitcoin-credit'

ELSE 'etherium-credit'

END AS transaction_type,

Sum(amount) AS amount

FROM cte1 o

GROUP BY category),

cte3

AS (SELECT Row_number()

OVER (

ORDER BY c.transaction_type) AS id,

c.*

FROM cte2 c)

SELECT f.*,

Sum(amount)

OVER(

ORDER BY f.id) AS total_coins

FROM cte3 f;