from thorchain.liquidity_actions

where lp_action = 'add_liquidity'

group by 1,2,3

),

remove_lp as (select from_address, pool_name, lp_action, sum(rune_amount) as rune_wd, sum(asset_amount) as asset_wd

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity'

group by 1,2,3

),

nett as (select a.from_address, a.pool_name, a.rune_add, b.rune_wd, a.asset_add, b.asset_wd

from add_lp a

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

final as (select from_address, pool_name, rune_add, case when rune_wd is null then 0 else rune_wd end as rune_withdraw,

asset_add, case when asset_wd is null then 0 else asset_wd end as asset_withdraw,

(rune_add - rune_withdraw) as net_rune, (asset_add - asset_withdraw) as net_asset

from nett),

bla as (select from_address, pool_name

from final

where net_rune > 0 or net_asset > 0),

blabla as (select from_address, count(pool_name) as pool_count

from bla

group by 1),

blablabla as (select from_address, pool_count

from blabla

where pool_count > 1)

select pool_name, count(from_address) as addresses_in_pools

from bla

where from_address in (select from_address from blablabla)