Snipertotal volume of bridge for Blocto Teleport and cBridge
Updated 2022-07-04
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 cet as ( select trunc(block_timestamp,'day') as day_ , tx_id , SUBSTRING(TOKEN_CONTRACT,CHARINDEX('.',TOKEN_CONTRACT,(charindex('.', TOKEN_CONTRACT, 1))+1)+1,99999) AS asset_name , direction , amount, bridge
from flow.core.fact_bridge_transactions)
,
tokens as ( select day_ ,
case when asset_name = 'ceWBTC' then 'WBTC'
when asset_name = 'ceWETH' then 'WETH'
when asset_name = 'ceUSDT' then 'USDT'
when asset_name = 'TeleportedTetherToken' then 'USDT'
when asset_name = 'ceBUSD' then 'BUSD'
when asset_name = 'ceBNB' then 'sBNB'
when asset_name = 'FlowToken' then 'FLOW'
when asset_name = 'ceFTM' then 'FTM'
when asset_name = 'ceMATIC' then 'MATIC'
when asset_name = 'StarlyToken' then 'STARLY'
when asset_name = 'TeleportedSportiumToken' then 'SPRT'
when asset_name = 'BloctoToken' then 'BLT'
else asset_name end as asset_name_ ,
tx_id , direction , amount ,bridge
from cet)
,
price1 as ( select trunc(TIMESTAMP,'day') as price1_daily , symbol, avg(price_usd) as price
from flow.core.fact_prices
where symbol in ( select asset_name_ from tokens)
group by 1,2)
,
price2 as ( select trunc(HOUR,'day') as price2_daily , symbol, avg(price) as price_
from ethereum.core.fact_hourly_token_prices
where symbol in ( select asset_name_ from tokens)
group by 1,2)
,
union_price as ( select day_, asset_name_, tx_id , bridge, direction , amount, amount*price as volume
from price1 T1 left outer join tokens T2 on T1.price1_daily = T2.day_
where asset_name_ = symbol
UNION
select day_, asset_name_, tx_id , bridge, direction , amount ,amount*price_ as volume
from price2 T1 left outer join tokens T2 on T1.price2_daily = T2.day_
Run a query to Download Data