from_address as address,

sum(rune_amount) as total_rune_added,

iff(charindex('-', pool_name) = 0, pool_name, LEFT(pool_name, CHARINDEX('-', pool_name) - 1)) as pool

from thorchain.liquidity_actions

where lp_action = 'add_liquidity'

and from_address is not null

group by from_address, pool

),

liquidity_remove as ( select

from_address as address,

sum(rune_amount) as total_rune_withdrawn,

iff(charindex('-', pool_name) = 0, pool_name, LEFT(pool_name, CHARINDEX('-', pool_name) - 1)) as pool

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity'

and from_address is not null

group by from_address, pool

),

net_liquidity_balance as (select

a.address,

a.pool,

a.total_rune_added - r.total_rune_withdrawn as lp_balance

from liquidity_add a, liquidity_remove r

where a.address = r.address

and a.pool = r.pool

and lp_balance > 0.001

),

n_providers_per_pool as (

select pool, count(*) as n_active_providers

from net_liquidity_balance

group by pool

),

final_values as (select pool,

count(case when lp_balance <= 10 then 1 end) as "Shrimp 0-10",