WITH contracts AS (

SELECT

MIN(tr.block_timestamp) AS create_timestamp,

tr.tx_hash,

tr.to_address AS contract

FROM monad.testnet.fact_traces tr

JOIN monad.testnet.fact_transactions t ON tr.tx_hash = t.tx_hash

WHERE tr.type ILIKE 'create%'

AND tr.tx_succeeded = TRUE

AND tr.trace_succeeded = TRUE

AND tr.to_address IS NOT NULL

GROUP BY 2, 3

),

user_metrics AS (

SELECT

t.from_address AS wallet_address,

COUNT(*) AS total_transactions,

COUNT(DISTINCT c.contract) AS unique_contracts,

SUM(tx_fee) AS total_fees,

COUNT(DISTINCT DATE(t.block_timestamp)) AS active_days

FROM monad.testnet.fact_transactions t

LEFT JOIN contracts c

ON t.to_address = c.contract

WHERE t.block_timestamp::date >= '2025-02-19'

GROUP BY 1

),

popular_contracts AS (

SELECT

from_address AS wallet_address,

to_address AS contract_address,

COUNT(tx_hash) AS tx_count,

ROW_NUMBER() OVER (PARTITION BY from_address ORDER BY COUNT(tx_hash) DESC) AS rank