with VOTING_POWERS as( SELECT DISTINCT ADDRESS, log(10, (avg(balance) + 1)) as amount --avg(balance) as amount FROM terra.daily_balances WHERE CURRENT_DATE >= dateadd(day, -30, CURRENT_DATE) AND BALANCE_TYPE = 'staked' AND BALANCE > 0 AND CURRENCY = 'LUNA' GROUP BY ADDRESS ), ranges as (select --terra.gov_vote.voter, --VOTING_POWERS.amount DISTINCT count(CASE WHEN VOTING_POWERS.amount > 0 AND VOTING_POWERS.amount <= 0.58 THEN VOTING_POWERS.address END) AS "Bin A, .01 - 2.80 VP", count(CASE WHEN VOTING_POWERS.amount > 0.58 AND VOTING_POWERS.amount <= 1.16 THEN VOTING_POWERS.address END) AS "Bin B, 2.80 - 13.45 VP", count(CASE WHEN VOTING_POWERS.amount > 1.16 AND VOTING_POWERS.amount <= 1.74 THEN VOTING_POWERS.address END) AS "Bin C, 13.45 - 53.95 VP", count(CASE WHEN VOTING_POWERS.amount > 1.74 AND VOTING_POWERS.amount <= 2.32 THEN VOTING_POWERS.address END) AS "Bin D, 53.95 - 207.93 VP", count(CASE WHEN VOTING_POWERS.amount > 2.32 AND VOTING_POWERS.amount <= 2.90 THEN VOTING_POWERS.address END) AS "Bin E, 207.93 - 793.33 VP", count(CASE WHEN VOTING_POWERS.amount > 2.90 AND VOTING_POWERS.amount <= 3.48 THEN VOTING_POWERS.address END) AS "Bin F, 793.33 - 3018.95 VP", count(CASE WHEN VOTING_POWERS.amount > 3.48 AND VOTING_POWERS.amount <= 4.06 THEN VOTING_POWERS.address END) AS "Bin G, 3018.95 - 11480.54 VP", count(CASE WHEN VOTING_POWERS.amount > 4.06 AND VOTING_POWERS.amount <= 4.64 THEN VOTING_POWERS.address END) AS "Bin H, 11480.54 - 43650.58 VP", count(CASE WHEN VOTING_POWERS.amount > 4.64 AND VOTING_POWERS.amount <= 5.22 THEN VOTING_POWERS.address END) AS "Bin I, 43650.58 - 165957.69 VP", count(CASE WHEN VOTING_POWERS.amount > 5.22 AND VOTING_POWERS.amount <= 5.80 THEN VOTING_POWERS.address END) AS "Bin J, 165957.69 - 630956.34 VP", count(CASE WHEN VOTING_POWERS.amount > 5.80 AND VOTING_POWERS.amount <= 6.38 THEN VOTING_POWERS.address END) AS "Bin K, 630956.34 - 2398831.92 VP", count(CASE WHEN VOTING_POWERS.amount > 6.38 AND VOTING_POWERS.amount <= 6.96 THEN VOTING_POWERS.address END) AS "Bin L, 2398831.92 - 9120107.39 VP", count(CASE WHEN VOTING_POWERS.amount > 6.96 AND VOTING_POWERS.amount <= 7.54 THEN VOTING_POWERS.address END) AS "Bin M, 9120107.39 - 34673684.00 VP" FROM VOTING_POWERS) 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" )) -- select * from terra.daily_balances limit 100

anadarkhani-l5jj2e