Eve Online is the game that no one has mentioned but that keeps coming to mind while I read this thread. It's the closest I've seen to a realistic social video game. When I saw your comment, I finally had to post something about it in this thread.
Like real life, doing anything meaningful in Eve is going to take a lot of time and planning. In some games, you can grind to max level in a matter of days or weeks. In Eve, max level is more than 15 years (roughly speaking). If you choose to focus your training on one activity, you will be weak in another until you put in the time to train it as well. Because of this, you are always behind your elders on an individual basis. This means that cooperation with other players is invaluable - no, imperative, if you want to make your mark or become effective quickly. Because of this, real social skills are incredibly useful. (Just like how, in the real world, networking is almost everything.)
Trust is the biggest currency in the game - trust in your partner not to skim from your share of the profits, trust in your CEO to recruit quality people and not traitors, trust in your fleet commander to recognize good vs suicidal engagements, and trust in your alliance leader to keep diplomatic ties strong. However, because "it's a game" and creating alts is easy (and incentivised), it's incredibly easy to trick and be tricked, and reprisals against bad actors are often very difficult to enforce.
So, there is this constant tension between the inherent insecurity of trusting anyone and the huge power available to savvy individuals who are willing and able to persuade and inspire and cajole and threaten until they are at the head of an army 18,000 accounts strong. On top of this, the free market economy means that a player is not limited to pursuing combat power, but also economic power - opening up avenues of play to people who aren't particularly interested in blowing up spaceships but who very much like to think about micro and macro economics.
You mention Second Life; I have never played it, but I imagine that it suffered because of the lack of real conflict / meaningful objectives / the lack of mechanics and/or enforcement of limiting policies designed to keep players comfortable and happy. I.e., no trolling, griefing, market manipulation, etc.
Eve Online is famous for the near-purity of its sandbox nature. It's not perfect - there are still actions that can get you banned from Eve - but it is much more permissive than any other MMO I've played or heard of. When Eve players appear in other games in organized groups (Black Desert Online, Elite Dangerous, Star Citizen - at least on the forums), chaos, forum tears and often bannings ensue. The content in Eve is extracted from other players; the NPC quests coded by the developers are the stuff of jokes.
Actions in Eve have permanence. Ships that blow up are gone, along with their cargo. There are no safe spaces. Names cannot be changed. The years-long account training process discourages constant recycling of "main" characters, so names are remembered for what they have done. A famous reputation blown by an injudicious action or an momentary lapse in opsec is forever tainted.
There are some interesting results from this stew of influences.
One such result that I'm often reminded of is that democracy isn't effective at producing a stable Eve entity. People have tried it, and the bureaucracy/middle-management leads to slow decision making, which leads to playing catch-up and eventually to member bleed and implosion. One fairly effective model is anarchy - a group of experienced members that understand each other quite well, are are all very capable on their own, and all "find content" often and make the others aware of it without asking for permission or making excuse for misplays. A couple of groups with ties to 4chan operate very much like this, unsurprisingly - including one of the most feared (but not even close to largest) alliances in the game. Another, more common model is the dictatorship - a personality cult, where one member makes the calls and steers the group according to his will. He might have an inner circle that he delegates duties to or receives counsel from, but there is no question who is in charge, internally or externally. This group is nimble, able to react quickly and operate in a united fashion, but the responsibility often burns out the leader. With the leader not logging in and no organic transition to an equally respected replacement, the magic is lost and the group dissolves.
Another result is the remarkable approach of an Eve player's duties to "real life" duties. In Eve, one interviews people, one researches people's work histories, one hires people and one fires people. In Eve, one reviews weekly, monthly and quarterly economic reports, collect and pay taxes, and audits franchise managers. In Eve, one configures up forums and websites and chat servers and SSO authentication systems and automated alarm systems. In Eve, one inserts watermarks and cross-references IP logs and administers barium meal tests and more, as means to maintain infosec. Some people say they would never play Eve, because it isn't really a game, it's a job. Some people play Eve precisely because they thrive on real challenge. Some people do just fine playing Eve to blow up ships and never do a single one of the things above.
Another result is the overall tendency towards stagnation and lack of meaningful conflict. I'm not sure yet if this is simply because of the current game mechanics or because of a larger, systemic flaw that video games cannot inherently overcome (or because of human nature). Most of the game map is "nullsec", space open for the taking to any alliance of any size that wants to plant a flag there. At first, this was motivation enough. Later, people needed a reason to take and hold space, which usually meant "it needs to be profitable to hold space." When the devs made holding space very profitable, nullsec alliances started to make non-invasion pacts with their neighbors. They'd fight as fleets of ships, but not attack holdings. It made sense financially; people were making enough money with the space they had; no need to evict the neighbor. Trying to do so would start a war, and that would be more painful than making fat stacks at home. Eventually one coalition of alliances held half the map and another coalition held the other, and only play fights were happening. The devs made a number of mechanics changes in an attempt to encourage balkanization, and that, combined with an unprecedented announcement by an fabulously wealthy player (he made his money from a player-owned and developed in-game casino, btw) to make essentially limitless funds available to anyone who attacked the leading alliance of one of those super-coalitions lead to a large breakup in the nullsec geopolitical landscape. Unfortunately, now it appears that many feel it is no longer worth holding space under the new mechanics (attacking is easy, defending is hard, burnout is real). So if it is too hard, no one plays, and if it is too easy, everyone works out deals and it becomes boring.
So, it turns out that in the game with some of the most freeing mechanics and policies, people still get bored. The practical strategy (from a game design p.o.v.) is to tweak the game back and forth looking for the sweet spot where it is fun, but not too hard. From a theoretical standpoint, it's hard to know whether or not video games are fundamentally and inevitably boring, or whether a perfect game has simply not yet been created.
>realistically, if you do that you're not competing with other games, but with dating apps, job interviews, and the real social world.
Nail on the head.