with inflow as (select to_address, sum(rune_amount) as rune_inflow from thorchain.transfers where asset = 'THOR.RUNE' group by 1), outflow as (select from_address, sum(rune_amount) as rune_outflow from thorchain.transfers where asset = 'THOR.RUNE' group by 1), holdings as (select from_address, rune_inflow - ifnull(rune_outflow, 0) as holding_rune from inflow a left join outflow b on a.to_address = b.from_address), adding as (select from_address, pool_name, sum(stake_units) as add_liq from thorchain.liquidity_actions where lp_action = 'add_liquidity' group by 1,2), removing as (select from_address, pool_name, sum(stake_units) as remove_liq from thorchain.liquidity_actions where lp_action = 'remove_liquidity' group by 1,2), stake_unitz as (select a.from_address, a.pool_name, (add_liq - ifnull(remove_liq,0)) / total_stake as percentage from adding a left join removing b on a.pool_name = b.pool_name and a.from_address = b.from_address join thorchain.pool_block_statistics c on a.pool_name = c.asset where day in (select max(day) from thorchain.pool_block_statistics) and percentage > 0), --and lp_action = 'remove_liquidity' rune_lped as (select a.from_address, percentage * rune_liquidity as rune_lping from stake_unitz a join thorchain.daily_pool_stats b on a.pool_name = b.pool_name where day in (select max(day) from thorchain.daily_pool_stats)), lps as(select from_address, sum(rune_lping) as total_rune_lped from rune_lped group by 1), base as (select a.from_address, ifnull(holding_rune, 0) as rune_wallet, ifnull(total_rune_lped, 0) as rune_liquidity_pool, rune_wallet + rune_liquidity_pool as thor_holdings, case when thor_holdings > 1 and thor_holdings <= 100 then 'Fish > 1 and <= 100' when thor_holdings > 100 and thor_holdings <= 1000 then 'Shrimp > 100 and <= 1K' when thor_holdings > 1000 and thor_holdings <= 10000 then 'Crab > 1K and <= 10K' when thor_holdings > 10000 and thor_holdings <= 100000 then 'Dolphin > 10K and <= 100K' when thor_holdings > 100000 and thor_holdings <= 1000000 then 'Shark > 100K and <= 1M' when thor_holdings > 1000000 then 'Whale > 1M' else null end as total_rune_ranges, case when thor_holdings > 1 and thor_holdings <= 100 then '1' when thor_holdings > 100 and thor_holdings <= 1000 then '2' when thor_holdings > 1000 and thor_holdings <= 10000 then '3' when thor_holdings > 10000 and thor_holdings <= 100000 then '4' when thor_holdings > 100000 and thor_holdings <= 1000000 then '5' else '9' end as ordering from holdings a left join lps b on a.from_address = b.from_address where total_rune_ranges is not null and thor_holdings > 1) select total_rune_ranges, ordering, count(distinct(from_address)) as number_of_wallet from base group by 1,2 order by 2

$RUNE Distribution