select *

from flow.core.fact_bridge_transactions

where bridge='blocto'

),

blocto_in as

select date_trunc('day',block_timestamp) as day,

token_contract,

sum(amount) as daily_total_amount_in

from blocto_all

where direction='inbound'

group by day, token_contract

),

blocto_out as

select date_trunc('day',block_timestamp) as day,

token_contract,

sum(amount) as daily_total_amount_out

from blocto_all

where direction='outbound'

group by day, token_contract

),

blocto_token_contract_prices as

select date_trunc('day',timestamp) as day,

(case when token = 'Starly' then 'A.142fa6570b62fd97.StarlyToken'

when token = 'Blocto Token' then 'A.0f9df91c9121c460.BloctoToken'

when token = 'Sportium' then 'A.475755d2c9dccc3a.TeleportedSportiumToken'

when token = 'Flow' then 'A.1654653399040a61.FlowToken'

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

when token = 'USDT' then 'A.cfdd90d4a00f7b5b.TeleportedTetherToken' -- this line can be removed

end

) as token_contract,

avg(price_usd) as avg_daily_price_usd