jkhuhnke11 Number of Active Delegates Filtered
Updated 2023-04-13Copy Reference Fork
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
›
⌄
WITH num_props_active AS (
SELECT
ceil(count(distinct proposal_id)*0.1) as num_props
FROM "ETHEREUM"."CORE"."EZ_SNAPSHOT"
WHERE
space_id = 'opcollective.eth'
),
voted as (
SELECT
voter,
count(distinct id) as props_voted
FROM ethereum.core.ez_snapshot
GROUP BY voter
),
active as (
SELECT
voter,
case when props_voted > num_props THEN
'TRUE'
else 'FALSE'
END AS is_active
FROM voted a
JOIN num_props_active
INNER JOIN crosschain.core.address_tags t
ON t.address = LOWER(voter)
WHERE creator = 'jkhuhnke11'
AND blockchain = 'optimism'
AND tag_type = 'delegate_name'
)
SELECT
count(distinct voter) as delegate
FROM active
WHERE is_active = 'TRUE'
Run a query to Download Data