Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table cryptotransactionledger as
    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 
7 rows affected

select * from cryptotransactionledger;


ID
TRANSACTION_TYPEID
TRANSACTION_TYPE
AMOUNT
TOTALCOINS


1
1
bitcoin-credit
30
30

2
2
etherium-credit
20
50

3
3
bitcoin-debit
-10
40

4
4
etherium-debit
-5
35

5
1
bitcoin-credit
15
50

6
2
etherium-credit
10
60

7
4
etherium-debit
-5
55


WITH cte1
     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; 


ID
TRANSACTION_TYPE
AMOUNT
TOTAL_COINS


1
bitcoin-credit
35
35

2
etherium-credit
20
55

ID	TRANSACTION_TYPEID	TRANSACTION_TYPE	AMOUNT	TOTALCOINS
1	1	bitcoin-credit	30	30
2	2	etherium-credit	20	50
3	3	bitcoin-debit	-10	40
4	4	etherium-debit	-5	35
5	1	bitcoin-credit	15	50
6	2	etherium-credit	10	60
7	4	etherium-debit	-5	55