Updated 2023-03-16Copy Reference Fork
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 price as (select
date_trunc('day',RECORDED_HOUR) as date,
case
when id='bitcoin-avalanche-bridged-btc-b' then 'BTC.b'
when id='ethereum' then 'nETH'
when id='avalanche-2' then 'WAVAX'
when id='usd-coin' then 'nUSD'
when id='defi-kingdoms' then 'JEWEL'
end as token,
avg(close) as price
from crosschain.core.fact_hourly_prices
group by 1,2
),main as(select
date_trunc('day',a.block_timestamp) as date,
symbol,
origin_from_address as user,
a.tx_hash,
case when event_name='TokenDeposit' then event_inputs:amount*price/pow(10,DECIMALS) else 0 end as inflow_volume,
case when event_name='TokenRedeem' then event_inputs:amount*price*(-1)/pow(10,DECIMALS) else 0 end as outflow_volume
from avalanche.core.fact_transactions a join avalanche.core.fact_event_logs b on a.tx_hash = b.tx_hash
join avalanche.core.dim_contracts c on ADDRESS = event_inputs:token
join price d on a.block_timestamp::date = d.date and symbol = token
where a.tx_hash in (select tx_hash from avalanche.core.fact_token_transfers a join avalanche.core.dim_contracts b on a.contract_address = b.address
)
AND event_name in ('TokenDeposit','TokenRedeem')
and event_inputs:chainId = '53935'
)
select
date,
symbol,
count(DISTINCT user) as user_count,
count(tx_hash) as tx_count,
sum(inflow_volume) as in_flow_volume,
sum(outflow_volume) as out_flow_volume,
sum(inflow_volume) + sum(outflow_volume) as net_flow_volume
from main
Run a query to Download Data