DATE_TRUNC('day', block_timestamp) as day,

address,

AVG(voting_power) as voting_power_average

FROM terra.validator_voting_power

GROUP BY day, address

ORDER BY day DESC, voting_power_average DESC),

rank_table as (SELECT tv.*, vl.label, RANK() OVER(PARTITION BY day ORDER BY voting_power_average DESC) as rank FROM top_validators tv

LEFT JOIN (SELECT * FROM terra.validator_labels) vl ON

tv.address = vl.vp_address

WHERE day >= DATEADD(day, -60, GETDATE())

AND label IS NOT NULL)

SELECT *

FROM rank_table

WHERE rank <= 10

ORDER BY day DESC, voting_power_average DESC