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

,SWAP_TO_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 "SWAP VOLUME"

,count(distinct SWAP_FROM_MINT) as "SOLD TOKEN"

,count(distinct SWAP_TO_MINT) as "BOUGHT TOKEN"

,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

order by 2 desc

limit 20