I have a few thoughts on this.
The first is games like (or maybe this is the only game like this) Divinity Original Sin. On the one hand, it’s an amazing two player game and I and a buddy had a blast playing through it once and a half (when the enhanced edition came out). On the other, I can’t really get into it playing on my own, there is just too much management and not being able to split up things like who does what kind of crafting and such is a huge pain the ass. I’m a little worried actually, about the sequel because it sounds like they are diving even harder into the co-op stuff, which will probably be awesome when I and my buddy play, but maybe even less appealing as a single player game. That kind of bugs me.
There are other games, like Total War Warhammer, I enjoyed and got into on a level I never would have had I not played so much co-op. Those games I still enjoy in single player, though, but at least in the case of Warhammer I may not have played much single player had I not gotten so into co-op (and learning how to play with someone that already had a good grasp on the game and could offer suggestions). Those games I can enjoy just as much alone or with a friend.
However, I don’t believe I have ever run into a game (this may be due to me largely avoiding multiplayer games) that was only fun as a co-op game. Everything I had more fun with as a co-op game from Dominions to Divinity Original Sin was certainly somewhat to very enjoyable as a single player game, just more so as a multiplayer game. I’m sure they are out there, but generally if a game isn’t much fun on its own, it’s not really going to be much fun for me even with a friend and even if we’re playing it just to “make fun of it”. My gaming time is just more valuable than that - life is to short too play a game you aren’t absolutely thrilled with.