SELECT

validator_address,

SUM(amount) / POWER(10, 6) AS total_stake -- Converting the amount to SEI (from micro-SEI)

FROM

sei.gov.fact_staking

WHERE

action = 'delegate' -- Only considering delegation actions

GROUP BY

validator_address

),

reward_summary AS (

SELECT

validator_address,

SUM(amount) / POWER(10, 6) AS total_rewards -- Converting the rewards amount to SEI (from micro-SEI)

FROM

sei.gov.fact_staking_rewards

WHERE

tx_succeeded = TRUE -- Only considering successful transactions

AND action = 'withdraw_rewards' -- Only considering withdraw reward actions

GROUP BY

validator_address

SELECT

COALESCE(s.validator_address, r.validator_address) AS validator_address, -- Combining validators that may appear only in one table

COALESCE(s.total_stake, 0) AS total_stake, -- Display total stake, or 0 if none

COALESCE(r.total_rewards, 0) AS total_rewards -- Display total rewards, or 0 if none

FROM

stake_summary s

FULL OUTER JOIN

reward_summary r

ON s.validator_address = r.validator_address

ORDER BY

total_stake DESC;