SELECT block_timestamp::DATE AS date

, to_address

, COUNT(DISTINCT tx_hash) AS transactions

FROM polygon.core.fact_transactions

WHERE block_timestamp >= '2022-07-01'

GROUP BY 1, 2

),

rank_address AS (

SELECT date

, to_address

, transactions

, row_number() OVER(PARTITION BY date ORDER BY transactions DESC) AS rank

FROM date_address

SELECT date

, CASE

WHEN address_name IS NULL THEN to_address

ELSE address_name

END AS wallet

, transactions

FROM rank_address

LEFT JOIN polygon.core.dim_labels

ON to_address = address

WHERE rank <= 10