t.block_timestamp,

t.tx_hash,

t.from_address AS user,

l.contract_address,

l.contract_name,

labels.project_name

avalanche.core.fact_transactions t

JOIN

avalanche.core.ez_decoded_event_logs l ON t.tx_hash = l.tx_hash

LEFT JOIN

avalanche.core.dim_labels labels ON l.contract_address = labels.address

t.block_timestamp >= DATEADD('day', -90, CURRENT_DATE())

DATE_TRUNC('day', block_timestamp) AS "Date",

INITCAP(project_name) AS "DApp",

COUNT(DISTINCT user) AS "Users"

WHERE

project_name IS NOT NULL

AND LOWER(project_name) != 'wavax'

GROUP BY

1, 2

ROW_NUMBER() OVER (PARTITION BY "Date" ORDER BY "Users" DESC) <= 3

ORDER BY

"Date" DESC,

"Users" DESC;