from flipside_prod_db.crosschain.address_labels

where label_type = 'cex' and label_subtype = 'hot_wallet'

and blockchain in ('ethereum' , 'solana' , 'polygon' , 'algorand' )

,

ethereum as ( select date(block_timestamp) as date, tx_hash as tx, project_name as p, to_address as user, symbol, amount_usd

from ethereum.core.ez_token_transfers a join cex_address b on a.from_address = b.address

where blockchain = 'ethereum' and block_timestamp::date >= CURRENT_DATE - 180

UNION ALL

select date(block_timestamp) as date, tx_hash as tx, project_name as p, eth_to_address as user, 'ETH' as symbol, amount_usd

from ethereum.core.ez_eth_transfers a join cex_address b on a.eth_from_address = b.address

where blockchain = 'etehreum' and block_timestamp::date >= CURRENT_DATE - 180

,

holders as ( select min(symbol_out) as symbol, user , tx_hash

from ethereum.core.ez_dex_swaps a left outer join ethereum b on a.SENDER = b.user

where block_timestamp::date >= date

group by 2,3

select symbol , count(DISTINCT(tx_hash)) as total

from holders

where user not in ( select address from flipside_prod_db.crosschain.address_labels) and symbol is not null

group by 1

order by 2 desc

limit 10