from thorchain.liquidity_actions

where pool_name like '%RUNE%'

and lp_action = 'add_liquidity'

and rune_amount > 0),

tf_rune_amount as (select from_address, rune_amount

from thorchain.transfers

where rune_amount > 0),

tf_and_lp as (

select *

from lp_rune_amount

union

select *

from tf_rune_amount

),

tf_and_lp_sum_rune as (select from_address, sum(rune_amount) as total_rune_amount

from tf_and_lp

group by from_address)

select case when total_rune_amount > 0 and total_rune_amount < 10 then '1. Shrimp 0-10'

when total_rune_amount >= 10 and total_rune_amount < 100 then '2. Crab 10-100'

when total_rune_amount >= 100 and total_rune_amount < 500 then '3. Octopus 100-500'

when total_rune_amount >= 500 and total_rune_amount < 1000 then '4. Fish 500-1k'

when total_rune_amount >= 1000 and total_rune_amount < 10000 then '5. Dolphin 1k-10k'

when total_rune_amount >= 10000 and total_rune_amount < 100000 then '6. Shark 10k-100k'

when total_rune_amount >= 100000 and total_rune_amount < 1000000 then '7. Whale 100k-1M'

when total_rune_amount >= 1000000 then '8. Humpback >1M' else null end as tier,

count(distinct from_address) as holders

from tf_and_lp_sum_rune

group by tier