(

block_timestamp,

tx_hash,

RECEIVER_ID as dao,

ACTIONS:"predecessor_id" as voter,

parse_json(ACTIONS:"receipt":"Action":"actions")[0]:"FunctionCall":"method_name" as act,

parse_json(try_base64_decode_string(parse_json(ACTIONS:"receipt":"Action":"actions")[0]:"FunctionCall":"args")):"id" as proposal_id,

parse_json(try_base64_decode_string(parse_json(ACTIONS:"receipt":"Action":"actions")[0]:"FunctionCall":"args")):"action" as vote,

'-' as proposer

from near.core.fact_receipts

where 1=1

-- and tx_hash = 'ChSLk25Qh7YeWEgTwHFuqhZek334zWLF12SRybGeFYXX'

and RECEIVER_ID='marketing.sputnik-dao.near'

and act='act_proposal'

),

t2 as

(

select

PROPOSAL_ID,

case

when count_if(VOTE='VoteApprove') over (partition by PROPOSAL_ID) + count_if(VOTE!='VoteApprove') over (partition by PROPOSAL_ID) < 3 then 'Expired'

when count_if(VOTE='VoteApprove') over (partition by PROPOSAL_ID) > count_if(VOTE!='VoteApprove') over (partition by PROPOSAL_ID) then 'Approved'

when count_if(VOTE='VoteApprove') over (partition by PROPOSAL_ID) < count_if(VOTE!='VoteApprove') over (partition by PROPOSAL_ID) then 'Rejected'

else 'Expired'

end as outcome

from t

),

t3 as

(

select distinct PROPOSAL_ID, (outcome) from t2

),