new_tokens as (

select

token_out as tt,

-- first_value(platform),

min(block_timestamp) as first_swap,

min(block_number) as first_swap_block_number

from ethereum.defi.ez_dex_swaps

group by 1

-- order by first_swap desc

),

t as(

select

token_out as token_address,

LISTAGG(DISTINCT symbol_out, ',') as token_name,

count(DISTINCT(tx_hash)) as tx_num,

sum(amount_in) as buy,

min(block_number) as block_number,

LISTAGG(distinct symbol_in, ',') as buy_in_token

from ethereum.defi.ez_dex_swaps s

right join new_tokens n

on

s.block_number = n.first_swap_block_number

and

s.token_out = n.tt

where n.first_swap ::date >= current_date() - 30

group by 1

)