select

,SYMBOL

,avg(CLOSE) as price

from solana.core.ez_token_prices_hourly

where RECORDED_HOUR::date>=current_date-120

and SYMBOL not in ('cope','smrt','gear','boo') --Wrong Data

group by 1,2),

tab2 as (

select --BLOCK_TIMESTAMP::date as date

case

when BLOCK_TIMESTAMP>=current_date-60 then 'LAST TWO MONTHS' else 'TWO MONTHS BEFORE' end as time

,SWAP_FROM_AMOUNT

,SWAP_FROM_MINT

,SWAPPER

,TX_ID

from solana.core.fact_swaps

where BLOCK_TIMESTAMP>=current_date-120

and SWAP_PROGRAM in('jupiter aggregator v2','jupiter aggregator v3','jupiter aggregator v4')

)

select --date

time

,case

when SWAP_FROM_AMOUNT*price>0 and SWAP_FROM_AMOUNT*price<10 then 'Below 10$'

when SWAP_FROM_AMOUNT*price>=10 and SWAP_FROM_AMOUNT*price<100 then '10~100$'

when SWAP_FROM_AMOUNT*price>=100 and SWAP_FROM_AMOUNT*price<1000 then '100~1K$'

when SWAP_FROM_AMOUNT*price>=1000 and SWAP_FROM_AMOUNT*price<10000 then '1K~10K$'

when SWAP_FROM_AMOUNT*price>=10000 then 'Up To 10K$' end as dis_volume

,count(distinct SWAPPER) as traders

,count(distinct TX_ID) as trades

from tab1

left join tab2

on tab1.TOKEN_ADDRESS=tab2.SWAP_FROM_MINT

where time is not null

and dis_volume is not null