tb AS (

select

BLOCK_TIMESTAMP,

TX_ID,

DIRECTION,BLOCKCHAIN AS Chain,

BRIDGE,

FLOW_WALLET_ADDRESS,

case

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceFTM' then 'FTM'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceAVAX' then 'AVAX'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceUSDT'

or TOKEN_CONTRACT = 'A.cfdd90d4a00f7b5b.TeleportedTetherToken'

or TOKEN_CONTRACT = 'A.9c6f94adf47904b5.UsdcUsdtSwapPair' then 'USDT'

when TOKEN_CONTRACT = 'A.b19436aae4d94622.FiatToken'

or TOKEN_CONTRACT = 'A.0f9df91c9121c460.BloctoToken' then 'USD'

when TOKEN_CONTRACT = 'A.d01e482eb680ec9f.REVV' then 'REVV'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceBUSD' then 'BUSD'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceDAI' then 'DAI'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceWETH' then 'ETH'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceBNB' then 'BNB'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.ceMATIC' then 'MATIC'

when TOKEN_CONTRACT = 'A.142fa6570b62fd97.StarlyToken' then 'STARLY'

when TOKEN_CONTRACT = 'A.1654653399040a61.FlowToken' then 'FLOW'

when TOKEN_CONTRACT = 'A.231cc0dbbcffc4b7.RLY' then 'RLY'

end AS Token,

Amount

from

flow.core.ez_bridge_transactions

)

select

sum(case when Token!='USD' then Amount*Price else Amount end) AS "Volume (USD)",

count(DISTINCT FLOW_WALLET_ADDRESS) AS "Bridgoors Number",

"Volume (USD)"/"Bridgoors Number" AS "Average Volume Per User",

count(*) AS "Bridges Count",

avg(case when Token!='USD' then Amount*Price else Amount end) AS "Average Bridge Amount (USD)"