I think Borderlands series does this exceptionally well as they all had a very solid story and mechanics with a very rewarding loot system and of course you can do the whole game single player, even if some bosses can be really challenging.
Destiny 2 I have never played, I don't get a lot of time to play online shooters as I have two young kids, but destiny was a great base I felt.
Too many games are trying to emulate this games as a service fad, but they fail because the game is just not good enough, be it poor mechanics, emptyness, or poor loot rewards.
Or the biggest problem: support! I have played FO76 and I have to applaud how well Bethesda has turned the game around when many publishers (EA with Anthem) would just cut their losses and walk away. Support to keep the population engaged, bring new members in and old memebrs back is the key to a good MMO game. Destiny and Borderlands have had great DLC, and FO76 has had major overhauls which really helps keep things fresh