select

date_trunc(day, BLOCK_TIMESTAMP) as day,

case

when swap_from_mint = '7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr'

or swap_to_mint = '7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr' then 'POPCAT'

when swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263'

or swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' then 'BONK'

when swap_from_mint = 'ukHH6c7mMyiWCf1b9pnWe25TSpkDDt3H5pQZgZ74J82'

or swap_to_mint = 'ukHH6c7mMyiWCf1b9pnWe25TSpkDDt3H5pQZgZ74J82' then 'BOME'

end as MemeCoin,

count(DISTINCT TX_ID) as N_Swaps,

count(DISTINCT SWAPPER) as N_Users,

sum(SWAP_FROM_AMOUNT * b.price) as SWAP_USD

from

solana.defi.fact_swaps a

join solana.price.ez_prices_hourly b on a.SWAP_FROM_MINT = b.token_address

and date_trunc(hour, a.block_timestamp) = b.hour

and (

swap_from_mint in (

'7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr',

'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263',

'ukHH6c7mMyiWCf1b9pnWe25TSpkDDt3H5pQZgZ74J82'

)

or swap_to_mint in (

'7GCihgDB8fe6KNjn2MYtkzZcRjQy3t9GHdC8uHYmW2hr',

'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263',

'ukHH6c7mMyiWCf1b9pnWe25TSpkDDt3H5pQZgZ74J82'

)

)

AND BLOCK_TIMESTAMP >= CURrENT_TIMESTAMP - INTERVAL '30 days'

group by

1,

2

having sum(SWAP_FROM_AMOUNT * b.price) < pow(10,8)

),