SELECT

TX_FROM AS user,

MIN(CAST(BLOCK_TIMESTAMP AS DATE)) AS first_transaction_date

FROM sei.core.fact_transactions

WHERE TX_SUCCEEDED = TRUE

GROUP BY TX_FROM

),

new_users_last_60_days AS (

SELECT

user

FROM new_users

WHERE first_transaction_date >= current_date - INTERVAL '60 days'

SELECT

MIN(num_transactions) AS min_transactions_by_new_users,

AVG(num_transactions) AS avg_transactions_by_new_users,

MAX(num_transactions) AS max_transactions_by_new_users,

SUM(num_transactions) AS total_transactions_by_new_users,

COUNT(DISTINCT user) AS total_new_users

FROM (

SELECT

TX_FROM AS user,

COUNT(DISTINCT TX_ID) AS num_transactions

FROM sei.core.fact_transactions

WHERE TX_SUCCEEDED = TRUE

AND TX_FROM IN (SELECT user FROM new_users_last_60_days)

GROUP BY TX_FROM

) as transaction_summary;