with tab1 as (

select

,SYMBOL

,avg(CLOSE) as price_usd

from solana.core.ez_token_prices_hourly

where RECORDED_HOUR::date>='2023-07-01'

and SYMBOL<>'boo'

group by 1,2),

tab2 as (

select BLOCK_TIMESTAMP::date as date

,SWAP_FROM_AMOUNT

,SWAP_FROM_MINT

,SWAPPER

,TX_ID

from solana.core.fact_swaps

where SWAP_PROGRAM in('orca token swap','ORCA Token Swap V2','orca whirlpool program')

and date>='2023-07-01'

and SUCCEEDED='true')

select

SWAPPER

,sum(SWAP_FROM_AMOUNT*price_usd)as volume

,count(distinct SWAPPER) as swappers

,count(distinct TX_ID) as swaps

from tab1

left join tab2

on tab1.TOKEN_ADDRESS=tab2.SWAP_FROM_MINT

where SWAP_FROM_AMOUNT is not null

group by 1)

select avg(volume) as avg

from act1