with new_contract_date as (

select contract_address,

min(BLOCK_TIMESTAMP) as min_block_time

from avalanche.core.fact_event_logs

group by contract_address

order by 2

SELECT PROJECT_NAME, date, tx_count, num_active_addresses, total_avax_gas, r

FROM (

SELECT initcap(c.PROJECT_NAME) as PROJECT_NAME,

DATE_TRUNC('day', tx.BLOCK_TIMESTAMP) AS date,

COUNT(DISTINCT tx_hash) as tx_count,

SUM(TX_FEE) as total_avax_gas,

COUNT(DISTINCT(tx.FROM_ADDRESS)) as num_active_addresses,

RANK() OVER (PARTITION BY date ORDER BY tx_count DESC) AS r

FROM avalanche.core.fact_transactions tx

INNER JOIN avalanche.core.dim_labels c

ON tx.TO_ADDRESS = c.address

where date(BLOCK_TIMESTAMP) >= CURRENT_DATE - 30

-- and TO_ADDRESS in (SELECT contract_address from new_contract_date)

GROUP BY PROJECT_NAME, date

) t

WHERE r <= 10

and tx_count >= 1000

ORDER BY PROJECT_NAME, date, tx_count DESC