SELECT DISTINCT tx_signer

FROM near.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - INTERVAL '2 years'

AND block_timestamp < CURRENT_DATE - INTERVAL '1 year'

),

active_addresses_this_year AS (

SELECT DISTINCT tx_signer

FROM near.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - INTERVAL '1 year'

AND block_timestamp < CURRENT_DATE

),

active_addresses_both_years AS (

SELECT DISTINCT a.tx_signer

FROM active_addresses_last_year a

JOIN active_addresses_this_year b ON a.tx_signer = b.tx_signer

COUNT(DISTINCT a.tx_signer) AS "Active Addresses This Year (From Last Year)",

COUNT(DISTINCT b.tx_signer) AS "Total Active Addresses Last Year",

ROUND(

(COUNT(DISTINCT a.tx_signer) * 100.0) / NULLIF(COUNT(DISTINCT b.tx_signer), 0),

2

) AS "Retention Rate (%)"

FROM active_addresses_last_year b

LEFT JOIN active_addresses_both_years a ON a.tx_signer = b.tx_signer;