How to check if value exists in each group (after group by)

Try this query:

SELECT uid 
FROM subscribes 
GROUP BY uid 
HAVING COUNT(*) > 2
   AND max( CASE "subscription_type"  WHEN 'type1' THEN 1 ELSE 0 END ) = 0