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, project_name , to_address as user, symbol, amount_usd

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

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

UNION ALL

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

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

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

,

solana as ( select date(block_timestamp) as date,tx_id as tx_hash, project_name , tx_to as user , amount, mint

from solana.core.fact_transfers a join cex_address b on a.tx_from = b.address

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

,

sol_label as ( select date, tx_hash, project_name ,user ,label , amount, address

from solana a join solana.core.dim_labels b on a.mint = b.address

,

sol_price as ( select date(block_timestamp) as p_date, label as symbol , (sum(SWAP_TO_AMOUNT)/sum(SWAP_FROM_AMOUNT)) as price

from solana.core.fact_swaps a join sol_label b on a.SWAP_FROM_MINT = b.address

where SWAP_FROM_MINT = address

and SWAP_TO_MINT = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

and p_date >= CURRENT_DATE - 180

and SWAP_TO_AMOUNT > 0 and SWAP_FROM_AMOUNT > 0

group by 1,2 )

,

solana_final as ( select date, tx_hash, project_name , user, symbol , amount , price, amount*price as amount_usd

from sol_price a left outer join sol_label b on a.p_date = b.date and a.symbol = b.label)

,

algorand as ( select date(block_timestamp) as date,tx_id as tx_hash, project_name , RECEIVER as user , amount, asset_id as asset

from flipside_prod_db.algorand.transfers a join cex_address b on a.TX_SENDER = b.address