anomoneANCHOR CTE - With Total Voters code
Updated 2022-02-20
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
›
⌄
/*
SELECT COUNT(DISTINCT "Whale voters") FROM
(
--Unique whale voters who have voted on Terra
SELECT DISTINCT address as "Whale voters" FROM terra.daily_balances a
INNER JOIN terra.gov_vote V
on a.address = v.voter
WHERE balance_usd >= 1000000
AND (date_trunc('day', block_timestamp) > date_trunc('day', getdate()) - interval '90 days')
UNION
--Unique whale voters who have voted on Anchor and Mirror
SELECT DISTINCT address as "Whale voters" FROM terra.daily_balances a
INNER JOIN terra.msg_events as e
on a.address = e.EVENT_ATTRIBUTES:"voter"
WHERE balance_usd >= 1000000
AND (date_trunc('day', block_timestamp) > date_trunc('day', getdate()) - interval '90 days')
AND EVENT_ATTRIBUTES:"contract_address" in ('terra1f32xyep306hhcxxxf7mlyh0ucggc00rm2s9da5', 'terra1wh39swv7nq36pnefnupttm2nr96kz7jjddyt2x' ) -- Anchor and Mirror voting contract addresses respectively
AND EVENT_ATTRIBUTES:"action" = 'cast_vote' AND event_type = 'from_contract'
)
SELECT DISTINCT address as "Whale voters" FROM terra.daily_balances a
INNER JOIN terra.gov_vote V
on a.address = v.voter
WHERE balance_usd >= 1000000
AND (date_trunc('day', block_timestamp) > date_trunc('day', getdate()) - interval '90 days')
UNION
SELECT DISTINCT address as "Whale voters"
FROM terra.daily_balances a
INNER JOIN terra.msg_events as e
on a.address = e.EVENT_ATTRIBUTES:"voter"
WHERE balance_usd >= 1000000