ESO has a sub option but it is not sub based.
EQ2 also still has a subscription option.
So I went back to FFXI about five years, albeit on a clone server (shhh, don't tell SE). The server was "locked" at level 75. Content was being added, but at the time they were up to Chains of Promathia. However, they made QoL changes that made the game more playable in today's world. Experience was bumped up by 50% and they added a way to get quest experience that did exist in the retail game, but wasn't added until much later in its lifetime. They also added the ability to port to certain places, but in the original game required certain jobs and spells. Of course, you still can't switch jobs on the fly - so they kept that aspect the same.
Since the server population was so low, there was a sense of community that I hadn't seen in FFXI or really any MMO since EQ1. If we wanted to do end-game content (sky, sea, etc), we needed more people to clear the MSQ. Lots of people that already cleared the MSQ were volunteering to help others get through some of the harder instances. Getting carried in that game really isn't a thing. The instances (battlegrounds) required all 6 players working together and every instance was level capped.
In a pinch, they had the equivalent of GMs (just other players) that would essentially act like party/duty finder. Let's say I did all the requirements needed to get up to some difficult instance in open up the raid zone. You'd shout for help in the worldwide linkshell and people would offer to come help. A "GM" would essentially port them directly to the instance to help you out. Then when you were done, the GM would port them back to what they were doing.
However... in today's world - I'd imagine 2006 "retail" FFXI would not survive. Like the OP said, too many time wasters.
Battle Royale/First Person Shooter-style games such as Fortnite, Apex and Valorant are social in that they allow players to group up.
The first problem I find with such games is that they are 'social' only if your friends are actually good at the game. Best to choose another game if you expect your team to win with that one friend who cannot seem to get things right. Or expect to lose a friend when they cannot meet your own expectations.
These games generate a lot of revenue, to be sure. They are, for all intents and purpose, the equivalent of football and basketball communities, where sports is the primary "hobby". Sports generate a lot more in revenue than video games.
MMORPGs provide a shared world, complete with shared experiences. Whether they also provided 'social' experiences is dependent on the players. Even World of Warcraft can be played Solo -- in fact the general experience of most players in an MMORPG is solo play, followed by social interactions through social media. I can go to Twitter or Tumblr or Instagram or Facebook or <insert favorite social media site here> and find people to share my enthusiasm for most any game out there. Whether we become friends, or remain mere acquaintances, depends on things outside of the game.