select

HOUR::date as date,

avg(price) as Price

from ethereum.core.fact_hourly_token_prices

where 1=1

and symbol = 'WETH'

and HOUR >= CURRENT_DATE - 365

group by 1

),

address as (

select

distinct USER_ADDRESS,

BALANCE/pow(10,18) * price as USD_Bal

from ethereum.core.fact_eth_balances a join ETH_price b on a.BLOCK_TIMESTAMP::date = b.date

where 1=1

and USD_Bal >= 1e7

select

BLOCK_TIMESTAMP::date as date,

concat(TOKEN0_SYMBOL, '-->', TOKEN1_SYMBOL) as "Swap Pair",

count(distinct TX_HASH) as "# Txs",

avg("# Txs") over(partition by "Swap Pair" order by date) as "Avg # Swaps"

from ethereum.uniswapv3.ez_swaps

where BLOCK_TIMESTAMP >= CURRENT_DATE - 30

and SENDER in (select USER_ADDRESS from address)

and TOKEN0_SYMBOL is not NULL

and TOKEN1_SYMBOL is not NULL

group by 1,2