WITH voting_power as( SELECT address, balance FROM terra.daily_balances WHERE date = (SELECT max(date) from terra.daily_balances) AND BALANCE_TYPE = 'staked' AND BALANCE > 0 AND CURRENCY = 'LUNA' ), power_distribution as ( SELECT PERCENTILE_DISC(0.10) within group(order by balance) over () as p10, PERCENTILE_DISC(0.20) within group(order by balance) over () as p20, PERCENTILE_DISC(0.30) within group(order by balance) over () as p30, PERCENTILE_DISC(0.40) within group(order by balance) over () as p40, PERCENTILE_DISC(0.50) within group(order by balance) over () as p50, PERCENTILE_DISC(0.60) within group(order by balance) over () as p60, PERCENTILE_DISC(0.70) within group(order by balance) over () as p70, PERCENTILE_DISC(0.80) within group(order by balance) over () as p80, PERCENTILE_DISC(0.90) within group(order by balance) over () as p90, PERCENTILE_DISC(0.95) within group(order by balance) over () as p95, PERCENTILE_DISC(0.99) within group(order by balance) over () as p99, PERCENTILE_DISC(0.999) within group(order by balance) over () as p999, PERCENTILE_DISC(1) within group(order by balance) over () as p100 FROM terra.daily_balances WHERE date = (SELECT max(date) from terra.daily_balances) AND BALANCE_TYPE = 'staked' AND BALANCE > 0 AND CURRENCY = 'LUNA' LIMIT 1 ) SELECT percentile, power from power_distribution unpivot (power for percentile in ( p10, p20, p30, p40, p50, p60, p70, p80, p90, p99, p100 ) ) order by power asc /* SELECT count(CASE WHEN v.balance > 0 AND v.balance <= p.p10 THEN v.address END) AS "0-10th Perc.", count(CASE WHEN v.balance > p.p10 AND v.balance <= p.p20 THEN v.address END) AS "10th - 20th Perc.", count(CASE WHEN v.balance > p.p20 AND v.balance <= p.p30 THEN v.address END) AS "20th - 30th Perc.", count(CASE WHEN v.balance > p.p30 AND v.balance <= p.p40 THEN v.address END) AS "30th - 40th Perc.", count(CASE WHEN v.balance > p.p40 AND v.balance <= p.p50 THEN v.address END) AS "40th - 50th Perc.", count(CASE WHEN v.balance > p.p50 AND v.balance <= p.p60 THEN v.address END) AS "50th - 60th Perc.", count(CASE WHEN v.balance > p.p60 AND v.balance <= p.p70 THEN v.address END) AS "60th - 70th Perc.", count(CASE WHEN v.balance > p.p70 AND v.balance <= p.p80 THEN v.address END) AS "70th - 80th Perc.", count(CASE WHEN v.balance > p.p80 AND v.balance <= p.p90 THEN v.address END) AS "80th - 90th Perc.", count(CASE WHEN v.balance > p.p90 AND v.balance <= p.p100 THEN v.address END) AS "90th - 100th Perc.", count(CASE WHEN 1> 2 THEN 1 END) AS "TEST" FROM voting_power as v INNER JOIN power_distribution p ; */ /* SELECT * FROM VOTING_POWERS; */ /* SELECT voter, avg(voting_power) as voting_power FROM terra.gov_vote WHERE block_timestamp > CURRENT_DATE - 30 and voting_power > 0 GROUP BY voter; */ /* SELECT * FROM terra.daily_balances where address = 'terra1p54hc4yy2ajg67j645dn73w3378j6k05v52cnk' AND currency = 'LUNA'; */ /* select * from ranges unpivot(counts for binning_ranges in ( "Bin A, .01 - 2.80 VP", "Bin B, 2.80 - 13.45 VP", "Bin C, 13.45 - 53.95 VP", "Bin D, 53.95 - 207.93 VP", "Bin E, 207.93 - 793.33 VP", "Bin F, 793.33 - 3018.95 VP", "Bin G, 3018.95 - 11480.54 VP", "Bin H, 11480.54 - 43650.58 VP", "Bin I, 43650.58 - 165957.69 VP", "Bin J, 165957.69 - 630956.34 VP", "Bin K, 630956.34 - 2398831.92 VP", "Bin L, 2398831.92 - 9120107.39 VP", "Bin M, 9120107.39 - 34673684.00 VP" )) */

Voting Power Distribution