Query editor
withquest_votes_total as (SELECTquest_contract_address, sum(votes_total) as quest_votes --max is 64 per voterFROM query_3361548 currWHERE curr.quest_contract_address IN (--m1-op mission quests. turn this into a values table after second mission launches.0x8b720e12e520d2b448e6b4c0fad55b8e08b64761,0x1c5dde828c9fc635e833eeba418278a264cc1a15,0x6e8601c0e57873139f1da5524eff876f93b22b6e)AND curr.mission_qualified > 0 --missions require minimum vote from key votersGROUP BY 1), votes_summary as (SELECTcurr.explorer, curr.username, sum(case when curr.quest_contract_address = 0x8b720e12e520d2b448e6b4c0fad55b8e08b64761then curr.votes_total else 0 end)as small_votes, sum(case when curr.quest_contract_address = 0x8b720e12e520d2b448e6b4c0fad55b8e08b64761then cast(curr.votes_total as double)/cast(tv.quest_votes as double) else 0 end)as small_votes_percent, sum(case when curr.quest_contract_address = 0x1c5dde828c9fc635e833eeba418278a264cc1a15then curr.votes_total else 0 end)as medium_votes, sum(case when curr.quest_contract_address = 0x1c5dde828c9fc635e833eeba418278a264cc1a15then cast(curr.votes_total as double)/cast(tv.quest_votes as double) else 0 end)as medium_votes_percent, sum(case when curr.quest_contract_address = 0x6e8601c0e57873139f1da5524eff876f93b22b6ethen curr.votes_total else 0 end)as large_votes, sum(case when curr.quest_contract_address = 0x6e8601c0e57873139f1da5524eff876f93b22b6ethen cast(curr.votes_total as double)/cast(tv.quest_votes as double) else 0 end)as large_votes_percentFROM query_3361548 currLEFT JOIN quest_votes_total tv ON curr.quest_contract_address = tv.quest_contract_addressWHERE 1=1AND curr.quest_contract_address IN (--m1-op mission quests. turn this into a values table after second mission launches.0x8b720e12e520d2b448e6b4c0fad55b8e08b64761,0x1c5dde828c9fc635e833eeba418278a264cc1a15,0x6e8601c0e57873139f1da5524eff876f93b22b6e)AND curr.mission_qualified > 0 --missions require minimum vote from key votersGROUP BY 1,2)SELECTexplorer, username, COALESCE(small_votes_percent*120,0) + COALESCE(medium_votes_percent*400,0) + COALESCE(large_votes_percent*1500,0) as op_rewards, small_votes, small_votes_percent, medium_votes, medium_votes_percent, large_votes