SELECT

tx_id,

block_timestamp,

pool_address,

provider_address AS user,

pool_name,

platform,

token_a_amount_usd + token_b_amount_usd AS amount_usd,

action_type AS event_name,

DATE_TRUNC('month', block_timestamp) AS month

FROM solana.marinade.ez_liquidity_pool_actions

),

monthly_users AS (

SELECT

month,

user

FROM main

GROUP BY month, user

),

retention AS (

SELECT

curr.month AS current_month,

COUNT(DISTINCT curr.user) AS total_users,

COUNT(DISTINCT CASE WHEN prev.user IS NOT NULL THEN curr.user END) AS retained_users

FROM monthly_users curr

LEFT JOIN monthly_users prev

ON curr.user = prev.user

AND curr.month = prev.month + INTERVAL '1 month'

GROUP BY curr.month

SELECT

current_month,

total_users,

retained_users,

ROUND((retained_users::DECIMAL / LAG(total_users) OVER (ORDER BY current_month)) * 100, 2) AS retention_rate