Select all subscriptions whose users don’t have any other active subscription

I am writing a subscription cleanup query that should select all canceled subscriptions, whose users don’t have any other active subscription (the application allows multiple subscriptions per user).

I started with this:

SELECT * FROM subscriptions s WHERE status = 'canceled' AND (SELECT COUNT(*) FROM subscriptions s2 where s2.user_id = s.user_id AND status = 'active') = 0;

However, I feel there must be an easier/more straightforward/more performant way.

Answer

NOT EXISTS can be more intuitive here:

SELECT * FROM subscriptions s WHERE status = 'canceled' 
AND NOT EXISTS 
(SELECT id FROM subscriptions s2 where s2.user_id = s.user_id AND status = 'active');

You can also use a LEFT JOIN here:

SELECT * 
FROM 
subscriptions s LEFT OUTER JOIN
subscriptions s2 ON s.USER_ID = s2.USER_ID AND s2.status='active'
WHERE status = 'canceled' AND s2.id IS NULL