select

hour::date as day,

avg(price) as ETH_Price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1

)

, swap as (

SELECT

date_trunc('{{Interval}}', block_timestamp)::date as date,

'Ethereum' as network,

'Uniswap' as dex,

count(distinct tx_hash) as Swaps_Cnt,

count(distinct from_address) as Swappers_Cnt,

sum(tx_fee * ETH_Price) as tx_fee_usd,

avg(tx_fee * ETH_Price) as avg_fee_usd,

sum(Swaps_Cnt) over (order by date) as Cum_TX_Cnt,

sum(tx_fee_usd) over (order by date) as Cum_fee_us

from ethereum.core.fact_transactions a join eth_price b on a.block_timestamp::date = b.day

where tx_hash in ( SELECT tx_hash from ethereum.uniswapv3.ez_swaps)

and block_timestamp::date > CURRENT_DATE - 181

and block_timestamp::date <= CURRENT_DATE - 1

group by 1,2,3

UNION

SELECT

date_trunc('{{Interval}}', block_timestamp)::date as date,

'Optimism' as network,

'Uniswap' as dex,

count(distinct tx_hash) as Swaps_Cnt,

count(distinct from_address) as Swappers_Cnt,

sum(tx_fee * ETH_Price) as tx_fee_usd,

avg(tx_fee * ETH_Price) as avg_fee_usd,

sum(Swaps_Cnt) over (order by date) as Cum_TX_Cnt,