select *

from solana.core.dim_labels

where label_type = 'cex'

where address = '9un5wqE3q4oCjyrDkwsdD48KteCJitQX5978Vh7KKxHo'

WITH dex_swaps AS (

SELECT swapper AS address

, SUM(CASE WHEN swap_from_mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3' THEN -swap_from_amount ELSE swap_to_amount END) AS net_swap_amout

, SUM(CASE WHEN swap_from_mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3' THEN swap_from_amount ELSE 0 END) AS swap_from_amount

, SUM(CASE WHEN swap_to_mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3' THEN swap_to_amount ELSE 0 END) AS swap_to_amount

, SUM(CASE WHEN swap_to_mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3' THEN 1 ELSE 0 END) AS n_swaps_to

FROM solana.defi.fact_swaps

WHERE block_timestamp >= '2023-11-19'

AND (

swap_from_mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3'

OR swap_from_mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3'

)

GROUP BY 1

), cex_transfers AS (

SELECT CASE WHEN lf.label_type = 'cex' THEN t.tx_to ELSE t.tx_from END AS address

, SUM(CASE WHEN lf.label_type = 'cex' THEN amount ELSE -amount END) AS net_cex_transfer_amout

FROM solana.core.fact_transfers t

LEFT JOIN solana.core.dim_labels lf

ON lf.address = t.tx_from

LEFT JOIN solana.core.dim_labels lt

ON lt.address = t.tx_to

AND label = 'okx'

WHERE block_timestamp >= '2023-11-19'

AND mint = 'HZ1JovNiVvGrGNiiYvEozEVgZ58xaU3RKwX8eACQBCt3'

AND (

COALESCE(lf.label_type, '') = 'cex'

OR COALESCE(lt.label_type, '') = 'cex'

)

AND NOT (