Using RH easily led you into an "rpm hell" of packages from various third-party repos that conflicted: https://en.wikipedia.org/wiki/Dependency_hell#Platform-speci...
Now they're both comparable. Yum/dnf are ok, the rpm repos are big. And apt actually has "apt", the tool, that consolidates those separate tools you mention. Before it had aptitude (since at least 2006 or so, I'd say) but it wasn't used much, but now apt is installed by default and you can use it. No more "apt-get", "apt-cache", etc.
So today, nothing makes one of them substantially better, IMO.
As a consequence, Debian packaging policy is (in my experience) a lot more aggressive about using detailed dependency-resolution metadata to establish constraints and define compatibility, which makes for a more annoying developer experience but a nicer end-user experience.
I started using it before 2005, before backports was a thing, and it was a lifesaver for my desktops, as I usually wanted to use newer versions from testing or unstable.
[1] https://www.debian.org/doc/manuals/debian-reference/ch02.en....
For apt as a format probably not so much anymore. For the Debian apt repositories it's the several thousand capable and driven maintainers.