It seems like most users got tired of the unknowns with CentOS and went to Alma/Rocky. Doesn't help that most third party software vendors also didn't bother to support it.

No, it is a totally different distro.

CentOS Stream employs a rolling-release model, which is much less stable than RHEL.

The previous main selling point of CentOS was bug-for-bug compatibility with RHEL. Red Hat is just killing the distro by moving their focus to a non-existent market. Enthusiasts will choose RHEL, while enterprises would choose the more stable RHEL, which Red Hat could earn money from, or alternatives like Alma or Rocky.