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

),

address AS (