MLDZMNwoc8
Updated 2023-02-18
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
›
⌄
with tb1 as (select
distinct RECEIVER as user1,
sum(AMOUNT/1e6) as total_recieved
from cosmos.core.fact_transfers
where TX_SUCCEEDED = 'TRUE' and CURRENCY='uatom'
and receiver ilike '%cosmos%'
group by 1),
tb2 as (select
distinct SENDER as user2,
sum(AMOUNT/1e6) as total_sent
from cosmos.core.fact_transfers s left join tb1 b on s.SENDER=b.user1
where TX_SUCCEEDED = 'TRUE' and CURRENCY='uatom'
and SENDER in (select user1 from tb1)
group by 1),
tb3 as (select
distinct user1 as users,
total_recieved - total_sent as net_atom
from tb1 s left join tb2 b on s.user1 = b.user2
where total_recieved>total_sent
and net_atom>=10000
)
select
case when voter in (select users from tb3) then 'Whales' else 'Ordinary users' end as user_type,
description,
count (distinct tx_id) as no_votes,
count (distinct voter) as no_voters,
count(distinct proposal_id) as no_proposals
from cosmos.core.fact_governance_votes a
left join cosmos.core.dim_vote_options b on a.vote_option = b.VOTE_OPTION
where TX_SUCCEEDED = 'TRUE'
--and voter in (select users from tb3)
group by 1,2
Run a query to Download Data