SELECT

validator_address as va1,

sum(CASE WHEN action LIKE 'Delegate' then amount ELSE 0 END) as delegation,

sum(CASE WHEN action LIKE 'Undelegate' then amount ELSE 0 END) as un_delegation

FROM terra.defi.ez_staking

---WHERE action like

GROUP BY 1

), tab2 as (

SELECT

VALIDATOR_ADDRESS as va2,

sum(amount) as redel_in

FROM terra.defi.ez_staking

GROUP BY 1

), tab3 as (

SELECT

sum(DELEGATION + REDEL_IN - UN_DELEGATION) as del_total

FROM tab1

left outer JOIN tab2

ON va1 = va2

), tab0 as (

SELECT

COUNT(DISTINCT proposal_id) as total_props

FROM terra.gov.fact_governance_votes

WHERE TX_SUCCEEDED = TRUE

), tab01 as (

SELECT

voter,

(COUNT(DISTINCT proposal_id) / avg(total_props)) * 100 as vote_percent,

COUNT(DISTINCT proposal_id) as votes

FROM terra.gov.fact_governance_votes

LEFT OUTER JOIN tab0

WHERE TX_SUCCEEDED = TRUE

GROUP BY 1

ORDER BY 2 DESC

), tab100 as (