WITH delegate_data AS ( SELECT validator_address , CASE WHEN validator_address = 'terravaloper1l920a79akuc98hz4389axf9ctcymrf5ays578x' THEN 'WildSage 🧙 0% all 2023!' WHEN validator_address = 'terravaloper12qvrr6j7u3z5el5uuwg99ftx3jru5py6027gfx' THEN 'Nodeasy' WHEN validator_address = 'terravaloper120ppepaj2lh5vreadx42wnjjznh55vvktp78wk' THEN 'Allnodes.com ⚡️ Auto-compound (Ledger or Keplr)' WHEN validator_address = 'terravaloper1k4ef8m95t7eq522evmmuzvfkpla04pezmu4j7k' THEN 'Bit Cat🐱' WHEN validator_address = 'terravaloper18vnm040mwk0d6plc60v5m9h2376gkcphknuwzs' THEN 'PRISM Protocol' WHEN validator_address = 'terravaloper1gfajnv0ud3r6kecvzzqm5acd2adntvgwmzzr72' THEN 'infstones' WHEN validator_address = 'terravaloper1hy99h5xn44jtn76dla93wsps8h2eqsxqah4zhj' THEN 'HEX TRUST' WHEN validator_address = 'terravaloper10ahmn0ucd9selcv77rwysx5pvd607z04zde7uh' THEN 'StakeLab' WHEN validator_address = 'terravaloper13g7z3qq6f00qww3u4mpcs3xw5jhqwraswraapc' THEN 'Everstake' WHEN validator_address = 'terravaloper1a9xxmdm7409sk0hjz4lzfjp52p4uft86lrqp4e' THEN 'Notional' -- WHEN validator_address IN ( --'terravaloper1n8c73565zd4tme5hhasvtzz8ad4nwxyex0ge7e' , 'terravaloper1a9j2g4z7cs6ag9jpwtrz4vt70j7xqh7lsd8zkz' , --'terravaloper1tf2s579f4jpfxc3nm92wl5up2aykpd3zlh8frt' , 'terravaloper17vead9akhss6ammrjraqnfzaqm7w7y999xvlcd' , --'terravaloper1wk4mkavc3uk74paz69wjxn9n6ltgaellyve5tg' , 'terravaloper1gk0qflzlph04eav2k37v22z2luzy7tq3zqh4a2' , --'terravaloper1yjxvqnaycgf0re2jz7rdsqq85urma88daddp6d' -- ) THEN 'No Data' ELSE iff(validator_label IS NULL ,'No Data In Terrasco' ,validator_label) END AS lable , sum(amount) AS total_delegate_amount , COUNT(DISTINCT delegator_address) AS delegator_count FROM terra.core.ez_staking WHERE ACTION = 'Delegate' AND tx_succeeded = TRUE GROUP BY 1,2 ), undelegate_data AS ( SELECT validator_address , CASE WHEN validator_address = 'terravaloper1l920a79akuc98hz4389axf9ctcymrf5ays578x' THEN 'WildSage 🧙 0% all 2023!' WHEN validator_address = 'terravaloper12qvrr6j7u3z5el5uuwg99ftx3jru5py6027gfx' THEN 'Nodeasy' WHEN validator_address = 'terravaloper120ppepaj2lh5vreadx42wnjjznh55vvktp78wk' THEN 'Allnodes.com ⚡️ Auto-compound (Ledger or Keplr)' WHEN validator_address = 'terravaloper1k4ef8m95t7eq522evmmuzvfkpla04pezmu4j7k' THEN 'Bit Cat🐱' WHEN validator_address = 'terravaloper18vnm040mwk0d6plc60v5m9h2376gkcphknuwzs' THEN 'PRISM Protocol' WHEN validator_address = 'terravaloper1gfajnv0ud3r6kecvzzqm5acd2adntvgwmzzr72' THEN 'infstones' WHEN validator_address = 'terravaloper1hy99h5xn44jtn76dla93wsps8h2eqsxqah4zhj' THEN 'HEX TRUST' WHEN validator_address = 'terravaloper10ahmn0ucd9selcv77rwysx5pvd607z04zde7uh' THEN 'StakeLab' WHEN validator_address = 'terravaloper13g7z3qq6f00qww3u4mpcs3xw5jhqwraswraapc' THEN 'Everstake' WHEN validator_address = 'terravaloper1a9xxmdm7409sk0hjz4lzfjp52p4uft86lrqp4e' THEN 'Notional' -- WHEN validator_address IN ( --'terravaloper1n8c73565zd4tme5hhasvtzz8ad4nwxyex0ge7e' , 'terravaloper1a9j2g4z7cs6ag9jpwtrz4vt70j7xqh7lsd8zkz' , --'terravaloper1tf2s579f4jpfxc3nm92wl5up2aykpd3zlh8frt' , 'terravaloper17vead9akhss6ammrjraqnfzaqm7w7y999xvlcd' , --'terravaloper1wk4mkavc3uk74paz69wjxn9n6ltgaellyve5tg' , 'terravaloper1gk0qflzlph04eav2k37v22z2luzy7tq3zqh4a2' , --'terravaloper1yjxvqnaycgf0re2jz7rdsqq85urma88daddp6d' -- ) THEN 'No Data' ELSE iff(validator_label IS NULL ,'No Data In Terrasco' ,validator_label) END AS lable , sum(amount) AS total_undelegate_amount , COUNT(DISTINCT delegator_address) AS undelegator_count FROM terra.core.ez_staking WHERE ACTION = 'Undelegate' AND tx_succeeded = TRUE GROUP BY 1,2 ), Redelegate_data_destination AS ( SELECT validator_address , CASE WHEN validator_address = 'terravaloper1l920a79akuc98hz4389axf9ctcymrf5ays578x' THEN 'WildSage 🧙 0% all 2023!' WHEN validator_address = 'terravaloper12qvrr6j7u3z5el5uuwg99ftx3jru5py6027gfx' THEN 'Nodeasy' WHEN validator_address = 'terravaloper120ppepaj2lh5vreadx42wnjjznh55vvktp78wk' THEN 'Allnodes.com ⚡️ Auto-compound (Ledger or Keplr)' WHEN validator_address = 'terravaloper1k4ef8m95t7eq522evmmuzvfkpla04pezmu4j7k' THEN 'Bit Cat🐱' WHEN validator_address = 'terravaloper18vnm040mwk0d6plc60v5m9h2376gkcphknuwzs' THEN 'PRISM Protocol' WHEN validator_address = 'terravaloper1gfajnv0ud3r6kecvzzqm5acd2adntvgwmzzr72' THEN 'infstones' WHEN validator_address = 'terravaloper1hy99h5xn44jtn76dla93wsps8h2eqsxqah4zhj' THEN 'HEX TRUST' WHEN validator_address = 'terravaloper10ahmn0ucd9selcv77rwysx5pvd607z04zde7uh' THEN 'StakeLab' WHEN validator_address = 'terravaloper13g7z3qq6f00qww3u4mpcs3xw5jhqwraswraapc' THEN 'Everstake' WHEN validator_address = 'terravaloper1a9xxmdm7409sk0hjz4lzfjp52p4uft86lrqp4e' THEN 'Notional' -- WHEN validator_address IN ( --'terravaloper1n8c73565zd4tme5hhasvtzz8ad4nwxyex0ge7e' , 'terravaloper1a9j2g4z7cs6ag9jpwtrz4vt70j7xqh7lsd8zkz' , --'terravaloper1tf2s579f4jpfxc3nm92wl5up2aykpd3zlh8frt' , 'terravaloper17vead9akhss6ammrjraqnfzaqm7w7y999xvlcd' , --'terravaloper1wk4mkavc3uk74paz69wjxn9n6ltgaellyve5tg' , 'terravaloper1gk0qflzlph04eav2k37v22z2luzy7tq3zqh4a2' , --'terravaloper1yjxvqnaycgf0re2jz7rdsqq85urma88daddp6d' -- ) THEN 'No Data' ELSE iff(validator_label IS NULL ,'No Data In Terrasco' ,validator_label) END AS lable , sum(amount) AS total_delegate_amount , COUNT(DISTINCT delegator_address) AS delegator_count FROM terra.core.ez_staking WHERE ACTION = 'Redelegate' AND tx_succeeded = TRUE GROUP BY 1,2 ), Redelegate_data_source AS ( SELECT validator_src_address , CASE WHEN validator_src_address = 'terravaloper1l920a79akuc98hz4389axf9ctcymrf5ays578x' THEN 'WildSage 🧙 0% all 2023!' WHEN validator_src_address = 'terravaloper12qvrr6j7u3z5el5uuwg99ftx3jru5py6027gfx' THEN 'Nodeasy' WHEN validator_src_address = 'terravaloper120ppepaj2lh5vreadx42wnjjznh55vvktp78wk' THEN 'Allnodes.com ⚡️ Auto-compound (Ledger or Keplr)' WHEN validator_src_address = 'terravaloper1k4ef8m95t7eq522evmmuzvfkpla04pezmu4j7k' THEN 'Bit Cat🐱' WHEN validator_src_address = 'terravaloper18vnm040mwk0d6plc60v5m9h2376gkcphknuwzs' THEN 'PRISM Protocol' WHEN validator_src_address = 'terravaloper1gfajnv0ud3r6kecvzzqm5acd2adntvgwmzzr72' THEN 'infstones' WHEN validator_src_address = 'terravaloper1hy99h5xn44jtn76dla93wsps8h2eqsxqah4zhj' THEN 'HEX TRUST' WHEN validator_src_address = 'terravaloper10ahmn0ucd9selcv77rwysx5pvd607z04zde7uh' THEN 'StakeLab' WHEN validator_src_address = 'terravaloper13g7z3qq6f00qww3u4mpcs3xw5jhqwraswraapc' THEN 'Everstake' WHEN validator_src_address = 'terravaloper1a9xxmdm7409sk0hjz4lzfjp52p4uft86lrqp4e' THEN 'Notional' -- WHEN validator_src_address IN ( --'terravaloper1n8c73565zd4tme5hhasvtzz8ad4nwxyex0ge7e' , 'terravaloper1a9j2g4z7cs6ag9jpwtrz4vt70j7xqh7lsd8zkz' , --'terravaloper1tf2s579f4jpfxc3nm92wl5up2aykpd3zlh8frt' , 'terravaloper17vead9akhss6ammrjraqnfzaqm7w7y999xvlcd' , --'terravaloper1wk4mkavc3uk74paz69wjxn9n6ltgaellyve5tg' , 'terravaloper1gk0qflzlph04eav2k37v22z2luzy7tq3zqh4a2' , --'terravaloper1yjxvqnaycgf0re2jz7rdsqq85urma88daddp6d' -- ) THEN 'No Data' ELSE iff(validator_src_label IS NULL ,'No Data In Terrasco' ,validator_src_label) END AS lable , sum(amount) AS total_delegate_amount , COUNT(DISTINCT delegator_address) AS delegator_count FROM terra.core.ez_staking WHERE ACTION = 'Redelegate' AND tx_succeeded = TRUE GROUP BY 1,2 ), final_Total_delegate_Luna AS ( SELECT a.validator_address , a.lable , a.delegator_count , a.total_delegate_amount + b.total_delegate_amount AS Total_delegate_Luna FROM delegate_data a LEFT JOIN Redelegate_data_destination b ON a.validator_address = b.validator_address ) , final_Total_undelegate_Luna AS ( SELECT a.validator_address , a.lable , a.undelegator_count , a.total_undelegate_amount + b.total_delegate_amount AS Total_undelegate_Luna FROM undelegate_data a LEFT JOIN Redelegate_data_source b ON a.validator_address = b.validator_src_address ), final_base_vote_power AS ( SELECT a.validator_address , a.lable , ABS(Total_delegate_Luna - Total_undelegate_Luna) AS tokens FROM final_Total_delegate_Luna a JOIN final_Total_undelegate_Luna b ON a.validator_address = b.validator_address ), final AS ( SELECT validator_address AS Operator_address , REPLACE(Operator_address, 'terravaloper', '') AS validator_likly , concat('terra',left(validator_likly, 34)) as validator_likly_wallet_address, -- These addresses are different from the real wallet address in the last 5 letters lable AS validator_name , tokens , (SELECT sum(tokens) FROM final_base_vote_power ) AS all_tokens , round((tokens / all_tokens) * 100,3) AS vote_power_percent FROM final_base_vote_power WHERE tokens > 0 ORDER BY vote_power_percent DESC -- for example validator : Allnodes.com ⚡️ Auto-compound (Ledger or Keplr) -- Operator_address = terravaloper120ppepaj2lh5vreadx42wnjjznh55vvktp78wk -- Wallet address for vote = terra120ppepaj2lh5vreadx42wnjjznh55vvkt + 5 letters ), final_validators AS ( SELECT Operator_address , validator_likly_wallet_address , validator_name , tokens , vote_power_percent FROM final ), --end ! inflow AS ( SELECT receiver , sum(amount/pow(10,6)) AS amount_in FROM terra.core.ez_transfers WHERE currency = 'uluna' AND tx_succeeded = TRUE GROUP BY 1 ), outflow AS ( SELECT sender , sum(amount/pow(10,6)) AS amount_out FROM terra.core.ez_transfers WHERE currency = 'uluna' AND tx_succeeded = TRUE GROUP BY 1 ), final_Approximate_balance AS ( SELECT receiver ,amount_in - amount_out AS net_balance_lunas , CASE WHEN net_balance_lunas >= 10000000 THEN 'Whale more then ~10m $Luna ' WHEN net_balance_lunas BETWEEN 1000000 AND 10000000 THEN 'Whale 1m ~ 10m $Luna ' WHEN net_balance_lunas BETWEEN 500000 AND 1000000 THEN 'Whale 500k ~ 1m $Luna ' WHEN net_balance_lunas BETWEEN 100000 AND 500000 THEN 'Whale 100k ~ 500k $Luna ' WHEN net_balance_lunas BETWEEN 50000 AND 100000 THEN 'Whale 50k ~ 100k $Luna ' WHEN net_balance_lunas BETWEEN 25000 AND 50000 THEN 'Whale 25k ~ 50k $Luna ' WHEN net_balance_lunas BETWEEN 10000 AND 25000 THEN '10k ~ 25k $Luna ' WHEN net_balance_lunas < 0 THEN 'Other' ELSE 'Other' END AS type_Of_voters FROM inflow a JOIN outflow b ON a.receiver = b.sender WHERE net_balance_lunas >= 0 ORDER BY 2 desc ), tx_counts_of_wallets AS ( SELECT tx_sender , COUNT(DISTINCT tx_id) AS total_tx FROM terra.core.fact_transactions WHERE tx_succeeded = TRUE GROUP BY 1 ), ENDs AS( SELECT date_trunc('hour',block_timestamp)AS hours , tx_id , voter , nvl(iff(validator_name IS NULL ,type_Of_voters,validator_name ),iff(total_tx <= 5 , 'For Vote Maybe || Total tx <= 5', 'Other')) AS Wallet_type, Operator_address ,nvl(vote_power_percent::string,' ') AS vote_power , nvl(round(net_balance_lunas , 2),0) AS Approximate_balance , CASE WHEN proposal_id = '3796' THEN 'Proposal 3796' WHEN proposal_id = '3795' THEN 'Proposal 3795' WHEN proposal_id = '3794' THEN 'Proposal 3794' WHEN proposal_id = '3665' THEN 'Proposal 3665' WHEN proposal_id = '3619' THEN 'Proposal 3619' END AS proposal_ids,vote_option_text FROM terra.core.fact_governance_votes a LEFT JOIN final_validators b ON substring(a.voter,1,len(a.voter)-5) = b.validator_likly_wallet_address LEFT JOIN final_Approximate_balance c ON a.voter = c.receiver JOIN tx_counts_of_wallets d ON a.voter = d.tx_sender WHERE proposal_id IN ('3796' , '3795' , '3794' , '3665' , '3619') -- last 5 proposal AND tx_succeeded = TRUE ), base AS ( SELECT hours , tx_id , voter , Wallet_type , CASE WHEN Wallet_type ilike 'Whale%' THEN 'Whale' WHEN Wallet_type ilike '%||%' THEN 'For Vote Maybe' WHEN Wallet_type ilike '10k%' THEN 'High Balance $Luna' WHEN Wallet_type ilike 'Other' THEN 'Normal Wallet' ELSE 'Validator' END AS Category , Operator_address ,vote_power ,Approximate_balance ,proposal_ids,vote_option_text FROM ENDs ) SELECT CASE WHEN Approximate_balance < 1 THEN 'Less Than 1 $Luna' WHEN Approximate_balance >= 1 AND Approximate_balance < 10 THEN '1 ~ 10 $Luna' WHEN Approximate_balance >= 10 AND Approximate_balance < 100 THEN '10 ~ 100 $Luna' WHEN Approximate_balance >= 100 AND Approximate_balance < 1000 THEN '100 ~ 1k $Luna' WHEN Approximate_balance >= 1000 AND Approximate_balance < 10000 THEN '1k ~ 10k $Luna' WHEN Approximate_balance >= 10000 AND Approximate_balance < 100000 THEN '10k ~ 100k $Luna' WHEN Approximate_balance >= 100000 AND Approximate_balance < 1000000 THEN '100k ~ 1m $Luna' WHEN Approximate_balance >= 1000000 AND Approximate_balance < 1000000 THEN '1m ~ 10m $Luna' WHEN Approximate_balance >= 10000000 AND Approximate_balance < 1000000 THEN '10m ~ 100m $Luna' ELSE 'More Than 100m $Luna ' END AS type, COUNT (DISTINCT voter) as users_count, (SELECT sum(Approximate_balance)/COUNT(DISTINCT voter) FROM base)AS averege_balance FROM base GROUP BY 1

Governance Grind