with add_lp as (select from_address, pool_name, sum(rune_amount_usd + asset_amount_usd) as Total_Supplied

from thorchain.liquidity_actions

where lp_action = 'add_liquidity' and from_address is not null

group by 1,2),

remove_lp as (select from_address, pool_name, sum(rune_amount_usd + asset_amount_usd) as total_removed

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity' and from_address in (select from_address from add_lp)

group by 1,2),

final as (select a.from_address, a.pool_name, a.total_supplied, b.total_removed, CASE

when b.total_removed is null then 0

else b.total_removed end as rem_lp,

(a.total_supplied - rem_lp) as current_lp_position_usd

from add_lp a

left outer join remove_lp b on a.from_address = b.from_address and a.pool_name = b.pool_name),

lasts as (select from_address, count(distinct pool_name) as current_pool, sum(current_lp_position_usd) as total_volume_lp

from final

group by 1

having total_volume_lp > 1)

select current_pool, count(distinct from_address) as address_count

from lasts

group by 1