select timestamp::date as day,

avg (price_usd) as usdprice

from near.core.fact_prices

where symbol = 'wNEAR'

group by 1),

block as (select

date_trunc (day,block_timestamp) as date,

block_id,

sum (transaction_fee) / 1e24 as Gas,

sum ((transaction_fee/1e24)*usdprice) as USD_Gas,

avg ((transaction_fee/1e24)*usdprice) as Average_USD_Gas

from near.core.fact_transactions t1 join nearpricet t2 on t1.block_timestamp::Date = t2.DAY

where block_timestamp >= CURRENT_DATE - 30

group by 1,2)

select date,avg(gas) as near_gas,avg(usd_gas) as avg_usd_gas

from block

group by date