Query editor
WITH current_support AS (SELECTCAST(JSON_VALUE(data, 'strict $[0]') as INTEGER) as referenda_id,COALESCE(TRY_CAST(JSON_VALUE(data, 'strict $[3].support') as UINT256),varbinary_to_uint256(FROM_HEX(JSON_VALUE(data, 'strict $[3].support')))) as support,length(JSON_VALUE(data, 'strict $[3].support')) as support_lenFROM moonbeam.eventsWHEREsection = 'referenda'AND method = 'DecisionStarted'),parsed_support AS (SELECTreferenda_id,support / pow(10, 18) as support_glmrFROM current_supportWHERE referenda_id BETWEEN 81 AND 87),ranked_support AS (SELECTreferenda_id,ROUND(support_glmr, 2) as support_glmr,CASEWHEN support_glmr > 0 THEN RANK() OVER (ORDER BY support_glmr DESC)ELSE NULLEND as support_rankFROM parsed_support)SELECTCASEWHEN referenda_id = 81 THEN 'Simon <3' --Simon KrausWHEN referenda_id = 82 THEN 'Michele' --AmurriWHEN referenda_id = 83 THEN 'Julio' --Pérez CabreraWHEN referenda_id = 84 THEN 'Ksenija' --KuzminaWHEN referenda_id = 85 THEN 'Marcel' --PérezWHEN referenda_id = 86 THEN 'Maksims' --KurajevsWHEN referenda_id = 87 THEN 'Yaron' --ZimmermannELSE 'Referendum ' || CAST(referenda_id as VARCHAR)END as proposal,support_glmr as "GLMR Support",CASEWHEN support_rank IS NOT NULL AND support_rank <= 3 THEN 'Elected'ELSE 'Not Elected'END as "Status"FROM ranked_supportORDER BY support_glmr DESC