How to I get a list of non subscribed users from this database?
It seems to me that you want the cartesian product between users and subscription_types, minus the subscriptions:

select distinct s.user, st.subscription_types
from subscriptions s
cross join subscription_types st
except
select user, sub_type
from subscriptions


This can, of course, be expressed in other ways, but this is probably the most obvious one.