SELECT address, label

FROM ethereum.core.dim_labels

WHERE label_type = 'cex'

),

combined_flows AS (

SELECT

date_trunc('day', block_timestamp) AS day,

label,

SUM(CASE WHEN from_address = cex_address.address THEN amount ELSE 0 END) -

SUM(CASE WHEN to_address = cex_address.address THEN amount ELSE 0 END) AS net

FROM ethereum.core.ez_token_transfers

JOIN cex_address ON from_address = cex_address.address OR to_address = cex_address.address

WHERE contract_address = LOWER('0x6c3ea9036406852006290770bedfcaba0e23a0e8')

AND block_timestamp::date >= '2023-08-20'

GROUP BY day, label

),

label_ranks AS (

SELECT

day,

label,

net,

RANK() OVER (PARTITION BY day ORDER BY net DESC) AS rnk

FROM combined_flows

),

aggregated_labels AS (

SELECT

day,

CASE

WHEN rnk <= 10 THEN label

ELSE 'Other'

END AS label,

SUM(net) AS net

FROM label_ranks

GROUP BY day, CASE WHEN rnk <= 10 THEN label ELSE 'Other' END