SELECT

REGEXP_SUBSTR(ADDRESS_NAME, '^[^[:space:]:]+') AS cex,

SUM(CASE WHEN FROM_ADDRESS = ADDRESS THEN AVAX_VALUE ELSE 0 END) AS outflow,

SUM(CASE WHEN TO_ADDRESS = ADDRESS THEN AVAX_VALUE ELSE 0 END) AS inflow

FROM avalanche.core.fact_transactions

JOIN avalanche.core.dim_labels

ON (avalanche.core.fact_transactions.FROM_ADDRESS = avalanche.core.dim_labels.ADDRESS OR avalanche.core.fact_transactions.TO_ADDRESS = avalanche.core.dim_labels.ADDRESS)

WHERE LABEL_TYPE = 'cex'

GROUP BY cex;