SELECT

from_address AS address,

DATE_TRUNC('month', block_timestamp) AS activity_month

FROM avalanche.core.fact_transactions

where from_address NOT IN ( select address from avalanche.core.dim_contracts)

UNION ALL

SELECT

to_address AS address,

DATE_TRUNC('month', block_timestamp) AS activity_month

FROM avalanche.core.fact_transactions

where to_address NOT IN ( select address from avalanche.core.dim_contracts)

),

MonthlyActiveUsers AS (

SELECT

activity_month,

address,

COUNT(*) AS transaction_count

FROM UserActivity

GROUP BY activity_month, address

),

NewUsers AS (

SELECT

address,

MIN(activity_month) AS first_active_month

FROM MonthlyActiveUsers

GROUP BY address

),

NewUserActivity AS (

SELECT

a.address,

a.activity_month,

b.first_active_month