FROM flipside_prod_db.crosschain.address_labels

WHERE label_type LIKE 'cex'

),

counts as (SELECT date_trunc('day', block_timestamp) as day,

PROJECT_NAME as address_name,

count(*) as trxs,

count(DISTINCT to_address) as wallets

FROM ethereum.core.fact_token_transfers

LEFT outer JOIN cex ON cex.address = from_address

WHERE from_address IN (SELECT address from cex)

AND block_timestamp::date >= CURRENT_DATE - 180

GROUP BY 1, 2

ORDER BY 3 DESC

LIMIT 10

)

SELECT date_trunc('day', block_timestamp) as day,

PROJECT_NAME,

count(*) as trxs,

count(DISTINCT to_address) as wallets

FROM ethereum.core.ez_token_transfers

LEFT outer JOIN cex ON cex.address = from_address

WHERE from_address IN (SELECT address from cex)

AND project_name in (SELECT address_name FROM counts)

AND block_timestamp::date >= CURRENT_DATE - 180

GROUP BY 1, 2