Bridge Volume - net flow

misaghlbBridge Volume - net flow

Updated 2022-07-03

with flow_price as (

SELECT date(TIMESTAMP) as date, SYMBOL, avg(PRICE_USD) as p from (

SELECT TIMESTAMP, SYMBOL, PRICE_USD from flow.core.fact_prices

UNION

SELECT HOUR as TIMESTAMP, case when SYMBOL is NULL then TOKEN_ADDRESS else symbol end as SYMBOL, PRICE as PRICE_USD

from ethereum.core.fact_hourly_token_prices

)

GROUP BY date, SYMBOL

in1 as (

SELECT date(a.block_timestamp) as date, a.bridge, sum(case when TOKEN_CONTRACT = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then AMOUNT else AMOUNT * c.p end) as vol

from flow.core.fact_bridge_transactions a

left join flow.core.dim_contract_labels b on b.EVENT_CONTRACT = a.TOKEN_CONTRACT

left join flow_price c on c.date = date(a.BLOCK_TIMESTAMP) and c.SYMBOL ilike case

when b.CONTRACT_NAME ilike 'ceAVAX' then '0x85f138bfEE4ef8e540890CFb48F620571d67Eda3'

when b.CONTRACT_NAME ilike 'ceBNB' then '0x418d75f65a02b3d53b2418fb8e1fe493759c7605'

when b.CONTRACT_NAME ilike 'TeleportedSportiumToken' then '0x5ab6a4f46ce182356b6fa2661ed8ebcafce995ad'

when substr(b.CONTRACT_NAME, 0, 2) = 'ce' then substr(b.CONTRACT_NAME, 3)

when b.CONTRACT_NAME ilike 'BloctoToken' then 'BLT'

when b.CONTRACT_NAME ilike '%Token' then substr(b.CONTRACT_NAME, 0, len(b.CONTRACT_NAME)-5)

else b.CONTRACT_NAME end

where DIRECTION='inbound'

-- where a.tx_id = 'd36bd2a92fe8cd2f2b564ca006976126c1a089c671719a976af0d344c870b75c'

GROUP by date(a.block_timestamp), a.bridge

out1 as (

SELECT date(a.block_timestamp) as date, a.bridge, sum(case when TOKEN_CONTRACT = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' then AMOUNT else AMOUNT * c.p end) * -1 as vol