select timestamp::date as day,

avg (price_usd) as usdprice

from near.core.fact_prices

where symbol = 'wNEAR'

group by 1),

neartransferst as (

select sum ((deposit/1e24)*usdprice) as USDTransfer

from near.core.fact_transfers t1 join nearpricet t2 on t1.block_timestamp::date = t2.day

where block_timestamp >= CURRENT_DATE - 30)

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

USDTransfer,

sum (transaction_fee) / 1e24 as Gas,

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

(pow(10,6)*USD_Gas)/USDTransfer as onemilliongas,sum(onemilliongas) over (order by daily) as cum_onemillion,sum(usd_gas) over (order by daily) as cum_gas_usd,sum(gas) over (order by daily) as cum_near_gas,usdprice

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

join neartransferst

where block_timestamp >= CURRENT_DATE - 30

and tx_status ilike 'Success'

group by 1,2,usdprice

order by 1