eth_price as (

SELECT

trunc(hour,'hour') as dates,

avg(price) as eth_price

from ethereum.core.fact_hourly_token_prices

where symbol='WETH' and hour>='2022-07-01'

group by 1

),

poly_price as (

SELECT

trunc(hour,'hour') as dates,

avg(price) as poly_price

from ethereum.core.fact_hourly_token_prices

where symbol='MATIC' and hour>='2022-07-01'

group by 1

),

poly as (

SELECT

trunc(block_timestamp,'hour') as date,

count(distinct tx_hash) as txs,

sum(tx_fee*poly_price) as daily_paid_fees_usd,

sum(txs) over (order by date) as cum_txs,

sum(daily_paid_fees_usd) over (order by date) as cum_paid_fees_usd,

avg(tx_fee*poly_price) as avg_fee_per_tx_usd

from polygon.core.fact_transactions x

join poly_price y on trunc(x.block_timestamp,'hour')=y.dates

where block_timestamp>='2022-07-03' and tx_fee is not null

group by 1

),

eth as (

SELECT

trunc(block_timestamp,'hour') as date,

count(distinct tx_hash) as txs,

sum(tx_fee*eth_price) as daily_paid_fees_usd,

sum(txs) over (order by date) as cum_txs,