SELECT

DATE_TRUNC('MONTH', BLOCK_TIMESTAMP) AS month,

PROVIDER_ADDRESS AS user,

ACTION_TYPE,

DEPOSIT_AMOUNT AS stake_amount,

-COALESCE(CLAIM_AMOUNT, 0) AS unstake_amount

FROM solana.marinade.ez_liquid_staking_actions

WHERE ACTION_TYPE IN ('deposit', 'depositStakeAccount', 'claim')

SELECT

month,

COUNT(CASE WHEN ACTION_TYPE IN ('deposit', 'depositStakeAccount') THEN user END) AS staking_actions,

COUNT(CASE WHEN ACTION_TYPE = 'claim' THEN user END) AS unstaking_actions,

SUM(stake_amount) AS total_stake_amount,

SUM(unstake_amount) AS total_unstake_amount,

COUNT(DISTINCT CASE WHEN stake_amount > 0 THEN user END) AS stakers,

COUNT(DISTINCT CASE WHEN unstake_amount < 0 THEN user END) AS unstakers,

COUNT(DISTINCT user) AS total_users

FROM txns

GROUP BY month

ORDER BY month DESC;