hessTotal ssss
Updated 2022-12-22
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with transfer_in as ( select trunc(block_timestamp,'week') as date, 'In' as type,currency, count(DISTINCT(tx_id)) as total_tx, sum(amount/pow(10,6)) as amounts, count(DISTINCT(sender)) as total_user,
avg(amount/pow(10,6)) as avg_volume, sum(total_tx) over (order by date asc) as cum_tx , sum(amounts) over (order by date asc) as cum_amount
from terra.core.ez_transfers
where currency in ('ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4',
'ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF')
and TRANSFER_TYPE = 'IBC_Transfer_In' and TX_SUCCEEDED = 'TRUE'
and message_type in ('/ibc.applications.transfer.v1.MsgTransfer','/cosmos.bank.v1beta1.MsgMultiSend',
'/cosmos.bank.v1beta1.MsgSend')
group by 1,2,3)
,
transfer_out as ( select trunc(block_timestamp,'week') as date, 'Out' as type,currency, count(DISTINCT(tx_id)) as total_tx, sum(amount/pow(10,6)) as amounts, count(DISTINCT(sender)) as total_user,
avg(amount/pow(10,6)) as avg_volume , sum(total_tx) over (order by date asc) as cum_tx , sum(amounts) over (order by date asc) as cum_amount
from terra.core.ez_transfers
where currency in ('ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4',
'ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF')
and TRANSFER_TYPE = 'IBC_Transfer_Off' and TX_SUCCEEDED = 'TRUE'
and message_type in ('/ibc.applications.transfer.v1.MsgTransfer','/cosmos.bank.v1beta1.MsgMultiSend',
'/cosmos.bank.v1beta1.MsgSend')
group by 1,2,3)
,
final as ( select *
from transfer_out
UNION
select *
from transfer_in
)
select type, case when currency = 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4' then 'USDC'
when currency = 'ibc/CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF' then 'USDT' end as tokens, sum(amounts) as volume , sum(total_tx) as total_txs,
sum(total_user) as users
from final
group by 1,2
Run a query to Download Data