DATE_TRUNC('week', BLOCK_TIMESTAMP) AS date,

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'

AND BLOCK_TIMESTAMP >= DATEADD('month', -6, CURRENT_DATE())

GROUP BY date, cex

ORDER BY date, cex;