select

date_trunc('hour',timestamp) as hour

,avg(price_usd) as flow_usd_price

,token_contract

,symbol

from flow.core.fact_prices

where token_contract = 'A.1654653399040a61.FlowToken'

group by 1, 3, 4

),

avg_swap as (

select

date_trunc('hour',block_timestamp) as hour

,sum(token_in_amount/token_out_amount) as avg_swap

from flow.core.ez_swaps

where token_out_contract = 'A.1654653399040a61.FlowToken'

and token_in_contract = 'A.142fa6570b62fd97.StarlyToken'

group by 1

--where token_in_contract ='A.231cc0dbbcffc4b7.ceBUSD' --flow.core.ez_token_transfers

--flow.core.fact_transactions

--flow.core.fact_events

--where tx_id ='c5e9b6be02157634afb0a19e84e9b5f092031afa7b8716340af360afbed4e7f4'

select

avg_swap.hour

,avg_swap

,avg_swap/flow_usd_price

from avg_swap

join flow_price on avg_swap.hour = flow_price.hour

group by 1, 2, 3