SELECT

sender,

max(block_timestamp) as last_transaction_date

FROM axelar.defi.ez_bridge_squid

WHERE block_timestamp BETWEEN '{{start_date}}' AND '{{end_date}}'

GROUP BY 1

),

dormant_users AS (

SELECT

sender,

last_transaction_date,

DATEDIFF('day', last_transaction_date, '{{end_date}}'::timestamp) as days_since_last_txn

FROM user_activity

WHERE DATEDIFF('day', last_transaction_date, '{{end_date}}'::timestamp) >= 60 -- 2 months (60 days)

AND last_transaction_date <= '{{end_date}}'

SELECT

COUNT(sender) as dormant_accounts,

MIN(days_since_last_txn) as min_dormant_days,

MAX(days_since_last_txn) as max_dormant_days,

AVG(days_since_last_txn) as avg_dormant_days

FROM dormant_users