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)

select from_address, count(distinct pool_name) as lp_count

from final

where net_rune > 0 or net_asset > 0

group by 1