WITH monthly_activity AS (

SELECT

DATE_TRUNC('month', block_timestamp) AS activity_month,

TX_SIGNER as from_address,

COUNT(DISTINCT tx_hash) AS transaction_count

FROM

near.core.fact_transactions

WHERE

block_timestamp >= CURRENT_DATE() - INTERVAL '1.5 years'

AND from_address NOT IN (

-- Exclude contract addresses

SELECT contract_address FROM near.core.dim_ft_contract_metadata

UNION

SELECT address from near.core.dim_address_labels

-- Add additional subqueries to exclude centralized exchanges or protocols

)

GROUP BY

activity_month,

from_address

HAVING

COUNT(DISTINCT tx_hash) >= 2

),

cohorts AS (

SELECT

activity_month,

from_address

FROM

monthly_activity

),

retention AS (

SELECT

c1.activity_month AS cohort_month,