from thorchain.transfers

group by 1),

send as (select from_address, sum(rune_amount) as sent

from thorchain.transfers

where from_address in (select to_address from receive)

group by 1),

total as (select a.to_address, (a.received - b.sent) as total_rune

from receive a

left outer join send b on a.to_address = b.from_address),

raw as (select to_address, total_rune, CASE

when total_rune < 10 then '1_normie'

when total_rune between 10 and 100 then '2_small_retail'

when total_rune between 100 and 1000 then '3_retail'

when total_rune between 1000 and 10000 then '4_mega_retail'

when total_rune between 10000 and 100000 then '5_whale'

when total_rune between 100000 and 1000000 then '6_mega_whale'

when total_rune between 1000000 and 10000000 then '7_hyper_whale'

else '8_ultra_whale' end as caterg

from total

where total_rune > 0)

select caterg, count(distinct to_address) as Counts

from raw

group by 1

order by 1 desc