SELECT

date(block_timestamp) AS date,

a.address,

CASE WHEN b.label IS NULL THEN a.address ELSE b.label END AS labeled,

median(voting_power) AS median_voting_power,

avg(voting_power) AS avg_voting_power,

row_number() over (PARTITION BY date ORDER BY median_voting_power DESC) AS rank

FROM terra.validator_voting_power a

LEFT JOIN terra.labels b ON a.address = b.address

WHERE DATEDIFF(day, block_timestamp, GETDATE()) BETWEEN 0 AND 30

GROUP BY 1,2,3

ORDER BY date, rank

),

STAKED_LUNA AS ( -- find total amount of staked luna

SELECT

date,

sum(balance) as luna_stake_balance

FROM terra.daily_balances

WHERE DATEDIFF(day, date, GETDATE()) between 0 and 60

AND BALANCE > 0 AND balance_type = 'staked' AND currency = 'LUNA'

AND address != 'terra1fl48vsnmsdzcv85q5d2q4z5ajdha8yu3nln0mh'

GROUP BY date

ORDER BY 1

date,

rank,

labeled,

sum(median_voting_power) as total_luna

FROM TOP_10

WHERE RANK < 11

GROUP BY 1,2,3

ORDER BY 1,2