CentOS was, as you say, a community developed drop-in replacement for RedHat RHEL that subsumed a number of earlier RPM-based distributions in the early-mid 2000s. It was _widely_ used: in the 2010s the CentOS install base was on the same scale as Debian.
The CentOS governance structure and trademarks were effectively absorbed by RedHat, Inc in 2014.
In 2020 RedHat unilaterally "refocused" CentOS from being essentially "Community RHEL" to being "CentOS Stream" which is more or less a stability channel somewhere between Fedora and RHEL. This doesn't suit the needs of many folks who were running CentOS, and was done so abruptly that they even broke support window promises on the current release at the time, so it generated some bad feelings.
AlmaLinux and RockyLinux are projects that promptly spun up to replace CentOS in the "Community RHEL" role, from different prominent members of the community: Alma from CloudLinux, Inc. and Rocky from Greg Kurtzer of [CentOS cofounder,Warewulf,Singularity/AppTainer, CIQ] fame.
I don't know all the inside politics, but the reason there are two appears to be partly related to concerns about governance structure to prevent another round of commercial capture of community resources.