SELECT

contract_address,

COUNT(*) AS total_transactions,

COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS unique_users,

SUM(CASE WHEN TX_STATUS = 'SUCCESS' THEN 1 ELSE 0 END) AS successful_transactions,

MIN(block_timestamp) AS first_interaction,

MAX(block_timestamp) AS last_interaction

FROM blast.core.fact_event_logs

WHERE block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

GROUP BY 1

SELECT

contract_address,

total_transactions,

unique_users,

successful_transactions,

first_interaction,

last_interaction,

ROUND(successful_transactions::NUMERIC / NULLIF(total_transactions, 0) * 100, 2) AS success_rate

FROM contract_stats

ORDER BY total_transactions DESC