Zanyar_98Fraud detection - Transparency Commission
    Updated 2023-10-04
    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