CryptoLionvoting2
Updated 2021-08-11
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 x as (
SELECT
sum(CASE WHEN action <> 'undelegate' THEN event_amount ELSE 0 END) as delegated_LUNA,
sum(CASE WHEN action = 'undelegate' THEN event_amount ELSE 0 END) as undelegated_LUNA,
delegated_LUNA - undelegated_LUNA as total_LUNA
FROM terra.staking
WHERE tx_status = 'SUCCEEDED'
LIMIT 1
),
-- SELECT *
-- FROM x
y as (
SELECT
validator_address_label,
count(DISTINCT delegator_address) as delegators,
sum(CASE WHEN action <> 'undelegate' THEN event_amount ELSE 0 END) as delegated_LUNA,
sum(CASE WHEN action = 'undelegate' THEN event_amount ELSE 0 END) as undelegated_LUNA,
delegated_LUNA - undelegated_LUNA as LUNA
FROM terra.staking
WHERE tx_status = 'SUCCEEDED'
GROUP BY 1
ORDER BY 5 DESC
LIMIT 10
),
z as (
SELECT
validator_address_label,
count(DISTINCT tx_id) as votes
FROM y
INNER JOIN terra.gov_vote on validator_address_label = voter_address_label
GROUP BY 1
ORDER BY 2 DESC
),
a as (
SELECT