Yamabe type equations with sign-changing nonlinearities on non-compact Riemannian manifolds