Zanyar_98Fraud detection - Transparency Commission
Updated 2023-10-04
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
›
⌄
WITH SUCCESSFUL_VOTES AS (
SELECT TX_HASH
FROM near.core.fact_receipts
WHERE RECEIVER_ID = 'elections.ndc-gwg.near'
AND actions['predecessor_id'] = 'elections.ndc-gwg.near'
AND object_keys(status_value)[0]::string != 'Failure'
AND actions['receipt']['Action']['actions'][0]['FunctionCall']['method_name']::string = 'on_vote_verified'
AND BLOCK_TIMESTAMP::DATE <= '2023-09-23'
),
VOTES_DETAILS_RAW_DATA AS (
SELECT TX_HASH, BLOCK_TIMESTAMP, SIGNER_ID AS VOTER, ARGS
FROM SUCCESSFUL_VOTES JOIN near.core.fact_actions_events_function_call USING(TX_HASH)
WHERE METHOD_NAME = 'on_vote_verified'
),
VOTES_DETAILAS AS (
SELECT TX_HASH, BLOCK_TIMESTAMP, VOTER, VALUE AS "Candidate name or Selected option for Budget Package", ARGS['prop_id'] AS TO_VOTE_ID,
CASE
WHEN TO_VOTE_ID = 1 THEN 'House of Merit'
WHEN TO_VOTE_ID = 2 THEN 'Council Of Advisors'
WHEN TO_VOTE_ID = 3 THEN 'Transparency Commission'
WHEN TO_VOTE_ID = 4 THEN 'Budget Package'
END AS "To Vote"
FROM VOTES_DETAILS_RAW_DATA, lateral flatten( input => VOTES_DETAILS_RAW_DATA.ARGS['vote'])
),
-- VOTES_OF_CANDIDATES AS (
-- SELECT COUNT(DISTINCT(TX_HASH)) AS "Number of votes",
-- "Candidate name or Selected option for Budget Package" AS "Candidate", "To Vote" AS "House name",
-- ROW_NUMBER() OVER (PARTITION BY "House name" ORDER BY "Number of votes" DESC) AS RANK
-- FROM VOTES_DETAILAS
-- WHERE "House name" != 'Budget Package'
-- GROUP BY "Candidate", "House name"
-- )
Run a query to Download Data