SELECT

SPLIT_PART(labels.ADDRESS_NAME, ' ', 1) AS base_name,

labels.ADDRESS_NAME AS full_cex_name,

DATE(transfers.INSERTED_TIMESTAMP) AS balance_date,

SUM(transfers.AMOUNT_USD) AS total_balance_usd

FROM base.core.ez_native_transfers AS transfers

JOIN base.core.dim_labels AS labels

ON transfers.TO_ADDRESS = labels.ADDRESS

WHERE labels.LABEL_TYPE = 'cex'

AND transfers.AMOUNT_USD > 0

AND transfers.INSERTED_TIMESTAMP >= CURRENT_DATE - INTERVAL '30 days'

GROUP BY base_name, full_cex_name, balance_date

SELECT

base_name,

MAX(full_cex_name) AS representative_cex_name,

balance_date,

SUM(total_balance_usd) AS total_balance_usd

FROM cex_groups

GROUP BY base_name, balance_date

ORDER BY balance_date, base_name;