token_price as (

select

date_trunc('day', hour) as date,

avg(price) as price

from ethereum.token_prices_hourly

where token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH

and hour >= '2022-01-01'

group by date

),

txns as (

select

t.gas_used,

price,

(t.gas_used * gas_price)/1e9 as gas_used_eth,

gas_used_eth * price as gas_used_usd,

s.*

from ethereum.sushi.ez_swaps s

join ethereum.core.fact_transactions t on s.tx_hash = t.tx_hash

join token_price p on p.date = s.block_timestamp::date

where t.block_timestamp >='2022-01-01' and s.block_timestamp >='2022-01-01'

select

block_timestamp::date as date,

avg(price) as eth_price,

sum(gas_used_eth) as daily_gas_used_eth,

sum(gas_used_usd) as daily_gas_used_usd,

sum(daily_gas_used_eth) over (order by date asc rows between unbounded preceding and current row) as cum_gas_used_eth,

sum(daily_gas_used_usd) over (order by date asc rows between unbounded preceding and current row) as cum_gas_used_usd

from txns