select date_trunc('hour', hour)

as hour,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

--from ethereum.core.fact_hourly_token_prices

where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH

and hour::date >=

current_date - 30

group by 1

),

ethereum_data as (

select sum(price*gas_used/pow(10,9)) / count(tx_hash) as usd

from ethereum.core.fact_transactions

inner join ROO on date_trunc('hour', block_timestamp) = hour

and block_timestamp::date >=

current_date - 30

),

sol_price as (

select date_trunc('hour', recorded_hour) as hour,

avg(close) as price

from solana.core.fact_token_prices_hourly

where symbol = 'SOL'

and recorded_hour::date >= current_date - 30

group by 1

),

solana_data as (

select sum(price*fee/pow(10,9)) / count(tx_id) as usd

from solana.core.fact_transactions

inner join sol_price on date_trunc('hour', block_timestamp) = hour

and block_timestamp::date >= current_date - 30

),