zakkisyedUntitled Query
Updated 2021-11-03
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
27
28
29
30
31
32
33
34
35
36
›
⌄
with pylon_governance as(
select block_timestamp, msg_value:sender::string as address, 0 as deposit_amount, msg_value:execute_msg:withdraw_voting_tokens:amount::float/1e6 as withdraw_amount from terra.msgs
where msg_value:contract::string = 'terra1xu8utj38xuw6mjwck4n97enmavlv852zkcvhgp'
and msg_value:execute_msg:withdraw_voting_tokens:amount is not null
union all
select block_timestamp, msg_value:sender::string as address, msg_value:execute_msg:send:amount::float/1e6 as deposit_amount, 0 as withdraw_amount from terra.msgs
where msg_value:contract::string = 'terra1kcthelkax4j9x8d3ny6sdag0qmxxynl3qtcrpy'
and msg_value:execute_msg:send:contract::string = 'terra1xu8utj38xuw6mjwck4n97enmavlv852zkcvhgp'
),
address_list as(
select address from (
select address, sum(deposit_amount) as deposit_amount, sum(withdraw_amount) as withdraw_amount, sum(deposit_amount) - sum(withdraw_amount) as net_deposit_amount from pylon_governance
group by address
)
where net_deposit_amount > 0
),
luna_staker as(
select distinct delegator_address, validator_address,
case when validator_address = 'terravaloper1jkg3wy5q9q6jlshjf2r6p9nf4flwtr6hp30rjk' then 'Neptune Finance'
when validator_address = 'terravaloper1mgdsc0get3w984h03a02zy6gmg3kgqtfqs3tky' then 'LambdaCore' else validator_address_label end as validator_address_label,
validator_address_name, validator_label_subtype, validator_label_type from terra.staking
where delegator_address in (select address from address_list)
and action in ('delegate', 'redelegate')
),
total_staker as(
select validator_address as validator, count(distinct delegator_address) as total_staker from terra.staking
where action in ('delegate', 'redelegate')
group by validator
)
select validator_address, validator_address_label, number_of_addresses from(
select validator_address, validator_address_label, number_of_addresses, row_number() over (order by number_of_addresses desc) as rownumber from(
select validator_address, validator_address_label, count(delegator_address) as number_of_addresses from luna_staker
group by validator_address, validator_address_label
order by number_of_addresses desc
)
)
Run a query to Download Data