from thorchain.liquidity_actions

where lp_action = 'add_liquidity'

group by 1,2),

remove as (select from_address, pool_name, sum(rune_amount_usd) as rune_usd, sum(asset_amount_usd) as asset_usd, (rune_usd + asset_usd) as total_remove_usd

from thorchain.liquidity_actions

where lp_action = 'remove_liquidity'

group by 1,2),

final as (select a.from_address, a.pool_name, a.total_add_usd, b.total_remove_usd, (a.total_add_usd - b.total_remove_usd) as current_lp

from addd a

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

select pool_name, count(distinct from_address), sum(current_lp) as Total_LP_currently

from final

where current_lp > 0

group by 1

order by 2 desc