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

),

outflow as (

select

BLOCK_TIMESTAMP::date as date,

sum(AMOUNT_USD) as "Outflow USD Volume"

from ethereum.core.ez_eth_transfers

where BLOCK_TIMESTAMP >= CURRENT_DATE - 30

and eth_FROM_ADDRESS in (select USER_ADDRESS from address)

and eth_to_address not in (select USER_ADDRESS from address)

group by 1

),

Inflow as (

select

BLOCK_TIMESTAMP::date as date,

sum(AMOUNT_USD) as "Inflow USD Volume"

from ethereum.core.ez_eth_transfers

where BLOCK_TIMESTAMP >= CURRENT_DATE - 30

and eth_to_ADDRESS in (select USER_ADDRESS from address)

and eth_from_address not in (select USER_ADDRESS from address)