SELECT

decoded_log:owner::STRING AS user_address,

COUNT(DISTINCT CASE WHEN EVENT_NAME = 'Deposit' THEN tx_hash END) AS deposit_count,

MAX(block_timestamp) AS last_activity_date,

DATEDIFF('day', MAX(block_timestamp), CURRENT_DATE()) AS days_inactive

FROM avalanche.core.fact_decoded_event_logs

WHERE contract_address = LOWER('0x06d47f3fb376649c3a9dafe069b3d6e35572219e')

AND EVENT_NAME IN ('Deposit')

GROUP BY user_address

),

loyalty_stats AS (

SELECT

COUNT(DISTINCT user_address) AS total_users,

COUNT(DISTINCT CASE WHEN deposit_count > 1 THEN user_address END) AS repeat_users,

COUNT(DISTINCT CASE WHEN days_inactive >= 30 THEN user_address END) AS dormant_users

FROM user_activity

'Repeat Stakers' AS "Metric",

repeat_users AS user_count,

ROUND((repeat_users * 100.0) / NULLIF(total_users, 0), 1) AS percentage

FROM loyalty_stats

UNION ALL

'Dormant Stakers' AS "Metric",

dormant_users AS user_count,

ROUND((dormant_users * 100.0) / NULLIF(total_users, 0), 1) AS percentage

FROM loyalty_stats

UNION ALL

'One-Time Stakers' AS "Metric",

(total_users - repeat_users) AS user_count,

ROUND(((total_users - repeat_users) * 100.0) / NULLIF(total_users, 0), 1) AS percentage