WITH activity_summary AS (

SELECT

CONTRACT_ADDRESS,

COUNT(DISTINCT TX_HASH) AS total_transactions,

COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS unique_users

FROM

avalanche.core.fact_event_logs

WHERE

BLOCK_TIMESTAMP >= '2024-12-16 17:00:00.000' -- activity since launch

GROUP BY

CONTRACT_ADDRESS

),

ranked_projects AS (

SELECT

activity.CONTRACT_ADDRESS,

labels.PROJECT_NAME AS project,

labels.LABEL_TYPE AS type,

activity.total_transactions AS tx_count,

ROW_NUMBER() OVER (PARTITION BY labels.PROJECT_NAME ORDER BY activity.total_transactions DESC) AS row_rank

FROM

activity_summary activity

INNER JOIN

avalanche.core.dim_labels labels

ON

activity.CONTRACT_ADDRESS = labels.ADDRESS

SELECT

project,

type,

tx_count

FROM

ranked_projects

WHERE

row_rank = 1 -- Select only the top-ranked entry per project

ORDER BY