alirsOsmos-G-09
Updated 2022-11-24
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
›
⌄
WITH One
AS (
SELECT block_timestamp::DATE AS DATE
,Description
,voter
FROM osmosis.core.fact_governance_votes vo
INNER JOIN osmosis.core.dim_vote_options op ON vo.vote_option = op.vote_id
WHERE tx_status = 'SUCCEEDED'
AND proposal_id = 362
)
,Two
AS (
SELECT DISTINCT vo.voter
,vo.block_timestamp
,op.Description
FROM osmosis.core.fact_governance_votes vo
INNER JOIN osmosis.core.dim_vote_options op ON vo.vote_option = op.vote_id
INNER JOIN one ON vo.voter = one.voter
AND vo.block_timestamp > one.DATE
AND op.description != one.description
WHERE tx_status = 'SUCCEEDED'
AND proposal_id = 362
)
SELECT CONCAT(one.description, ' ==> ' ,two.description) as Change_Type
,count(DISTINCT one.voter) AS Voters_Count
FROM one
INNER JOIN Two on one.voter = two.voter and one.description != two.description and one.date <two.block_timestamp
GROUP BY 1
order by 2 desc
Run a query to Download Data