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

COUNT(DISTINCT CASE WHEN FROM_ADDRESS = ADDRESS THEN FROM_ADDRESS END) AS outflow_users,

COUNT(DISTINCT CASE WHEN TO_ADDRESS = ADDRESS THEN TO_ADDRESS END) AS inflow_users,

COUNT(DISTINCT CASE WHEN FROM_ADDRESS = ADDRESS THEN TX_HASH END) AS outflow_transactions,

COUNT(DISTINCT CASE WHEN TO_ADDRESS = ADDRESS THEN TX_HASH END) AS inflow_transactions

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' AND BLOCK_TIMESTAMP >= DATEADD(month, -12, CURRENT_TIMESTAMP())

GROUP BY cex;