messariTest - validators
Updated 2022-04-29
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
›
⌄
-- with val_data as (
-- select validator_address, validator_address_label , count(distinct delegator_address) as unique_stakers, sum(event_amount_usd)/pow(10,6) as vol_in_m, date_trunc('day',block_timestamp) as date
-- from terra.staking
-- -- where date = CURRENT_DATE - 1
-- GROUP by validator_address, validator_address_label, date
-- order by vol_in_m desc, date asc)
-- select * from val_data
-- where date = CURRENT_DATE - 1
with net_balances as (select address, sum(balance_usd) as net_balance from terra.daily_balances
where date = CURRENT_DATE -1
GROUP by address )
select validator_address_label, count(*) as unique_del, sum(net_balance)/pow(10,6) as net_worth_of_validator, avg(net_balance) as avg_net_worth_of_validator
from
(select * from
(select * from
(select delegator_address, validator_address_label, sum(net_stake) as total_staked from
(select delegator_address, validator_address_label, case when action = 'delegate' then event_amount else event_amount*(-1) end as net_stake from terra.staking)
GROUP by 1,2 ) where validator_address_label is not NULL and total_staked > '0')
inner join net_balances
on delegator_address = address )
GROUP by 1
order by 3 desc
Run a query to Download Data