rajsLast 5 Proposals Analysis
Updated 2023-01-25
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 latest_proposals as
(
SELECT
proposal_id::text as proposal_id,
count(*) as no_of_votes,
count(distinct voter) as no_of_voters,
min(block_timestamp),
max(block_timestamp)
from terra.core.fact_governance_votes
where tx_succeeded
group by 1
order by 5 desc
limit 5
)
SELECT
*,
avg(no_of_voters) over (order by proposal_id rows between 29 preceding and current row) as "30_ma_avg_voters",
avg(no_of_votes) over (order by proposal_id rows between 29 preceding and current row) as "30_ma_avg_votes"
from
(
SELECT
proposal_id::text as proposal_id,
case when proposal_id in (select proposal_id from latest_proposals) then 'last_5_proposals' else 'not_in_last_5_proposals' end as category,
count(distinct voter) as no_of_voters,
avg(count(distinct voter)) over () as avg_voters_per_proposal,
median(count(distinct voter)) over () as median_voters_per_proposal,
count(distinct tx_id) as no_of_votes,
avg(count(distinct tx_id)) over () as avg_votes_per_proposal,
median(count(distinct tx_id)) over () as median_votes_per_proposal
from terra.core.fact_governance_votes
where tx_succeeded
group by 1,2
order by 1
)
Run a query to Download Data