SELECT

FROM_ADDRESS,

MIN(BLOCK_TIMESTAMP) AS FirstTransaction

FROM blast.core.fact_transactions

GROUP BY FROM_ADDRESS

HAVING MIN(BLOCK_TIMESTAMP) >= CURRENT_DATE - INTERVAL '60 DAY'

),

TransactionsLast30Days AS (

SELECT

BLOCK_TIMESTAMP,

FROM_ADDRESS,

FROM blast.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '30 DAY'

SELECT

SUM(CASE WHEN n.FROM_ADDRESS IS NOT NULL THEN 1 ELSE 0 END) AS TotalTransactionsByNewUsers,

SUM(CASE WHEN n.FROM_ADDRESS IS NULL THEN 1 ELSE 0 END) AS TotalTransactionsByOldUsers

FROM

(SELECT DISTINCT TX_HASH, FROM_ADDRESS FROM TransactionsLast30Days) t

LEFT JOIN

NewUsersLast60Days n

ON

t.FROM_ADDRESS = n.FROM_ADDRESS;