SELECT

signer_id,

SUM(CASE WHEN method_name = 'deposit_and_stake' THEN deposit * 1e-24 ELSE 0 END) AS total_staked,

COUNT(CASE WHEN method_name = 'deposit_and_stake' THEN 1 ELSE NULL END) AS staking_transactions,

SUM(CASE WHEN method_name IN ('unstake', 'unstake_all', 'liquid_unstake') THEN args:amount * 1e-24 ELSE 0 END) AS total_unstaked,

COUNT(CASE WHEN method_name in ('unstake', 'unstake_all', 'liquid_unstake') THEN 1 ELSE NULL END) AS unstaking_transactions

FROM

near.core.fact_actions_events_function_call

WHERE

receiver_id = 'meta-pool.near'

AND receipt_succeeded = 'TRUE'

GROUP BY

signer_id

),

TotalStaked AS (

SELECT

SUM(total_staked) AS total_staked_amount

FROM

StakingData

s.signer_id AS "Stakers",

s.total_staked AS "Staked",

s.total_unstaked as "Unstaked",

(s.total_staked - s.total_unstaked) AS "Net Staked",

s.staking_transactions AS "Staking Transactions",

s.unstaking_transactions AS "Unstaking Transactions",

ROUND((s.total_staked / t.total_staked_amount) * 100,2) || '%' AS "Percentage from Total Staked Amount"

FROM

StakingData s,

TotalStaked t

WHERE "Net Staked" IS NOT NULL

ORDER BY

"Net Staked" DESC

LIMIT 20;