from thorchain.liquidity_actions

where lp_action = 'remove_liquidity' and block_timestamp between '2021-11-17' and '2021-11-24'

group by 1,2),

xrune as (select from_address, sum(to_amount) as xRUNE_owned

from thorchain.swaps

where from_address in (select from_address from remove_lp) and to_asset = 'ETH.XRUNE-0X69FA0FEE221AD11012BAB0FDB45D444D3D2CE71C'

group by 1),

THOR as (select from_address, sum(to_amount) as THOR_owned

from thorchain.swaps

where from_address in (select from_address from remove_lp) and to_asset = 'ETH.THOR-0XA5F2211B9B8170F694421F2046281775E8468044'

group by 1)

select a.from_address, a.liquidity_removed, b.xrune_owned, c.thor_owned, case

when xrune_owned is not null and thor_owned is null then 'Owned_xrune'

when xrune_owned is null and thor_owned is not null then 'Owned_thor'

when xrune_owned is not null and thor_owned is not null then 'Owned_both'

when xrune_owned is null and thor_owned is null then 'Owned_none'

else null end as holdings

from remove_lp a

left outer join xrune b on a.from_address = b.from_address

left outer join thor c on a.from_address = c.from_address