SELECT

DATE_TRUNC('hour', block_timestamp) AS hour,

COUNT(DISTINCT from_address) AS hourly_wallets,

COUNT(tx_hash) AS hourly_transactions,

SUM(gas_used * gas_price) AS hourly_fees

FROM MONAD.testnet.fact_transactions

WHERE block_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())

GROUP BY DATE_TRUNC('hour', block_timestamp)

),

new_wallets_hourly AS (

SELECT

DATE_TRUNC('hour', first_tx_time) AS hour,

COUNT(DISTINCT from_address) AS new_wallets

FROM (

SELECT

from_address,

MIN(block_timestamp) AS first_tx_time

FROM MONAD.testnet.fact_transactions

WHERE block_timestamp >= DATEADD('day', -30, CURRENT_TIMESTAMP())

GROUP BY from_address

)

GROUP BY DATE_TRUNC('hour', first_tx_time)

SELECT

h.hour,

h.hourly_wallets AS "👛 Hourly Active Wallets",

COALESCE(n.new_wallets, 0) AS "🆕 New Wallets per Hour",

h.hourly_transactions AS "💫 Hourly Transactions",

h.hourly_fees AS "💰 Hourly Fees",

SUM(h.hourly_wallets) OVER (ORDER BY h.hour) AS "📈 Cumulative Wallets (30d)",

SUM(h.hourly_transactions) OVER (ORDER BY h.hour) AS "🔄 Cumulative Transactions (30d)",

SUM(h.hourly_fees) OVER (ORDER BY h.hour) AS "💎 Cumulative Fees (30d)"

FROM hourly_metrics h

LEFT JOIN new_wallets_hourly n ON h.hour = n.hour

ORDER BY h.hour;