select

distinct TOKEN_CONTRACT as token_contract,

REPLACE(SUBSTRING(token_contract, CHARINDEX('.', token_contract,3), LEN(token_contract)), '.', '') as token_names,

replace(token_names,'ce','') as main1,

replace(main1,'BloctoToken','BLT') as t1,

replace(t1,'FlowToken','FLOW') as t2,

replace(t2,'StarlyToken','STARLY') as t3,

replace(t3,'TeleportedSportiumToken','SPRT') as main2,

replace(main2,'TeleportedTetherToken','USDT') as main3

from flow.core.fact_bridge_transactions

),

main1 as (

select

date_trunc('day',BLOCK_TIMESTAMP) AS date,*

from tokens tokens

join flow.core.fact_bridge_transactions tx

on tokens.token_contract=tx.TOKEN_CONTRACT

),

price1 as (

select

date_trunc('day',TIMESTAMP) AS date,

SYMBOL,

avg(PRICE_USD) as usd_price

from flow.core.fact_prices

where SYMBOL in (select main3 from tokens )

group by 1,2

),

price2 as (

select

date_trunc('day', HOUR) AS date,

SYMBOL,

avg(PRICE) as usd_price