SteamOS definitely isn’t made for a traditional desktop computer. It has a desktop environment for when you need it, but that’s basically bolted onto the side of Steam’s Big Picture mode. It would likely work well if you have a dedicated PC for your living room TV. But for a traditional desktop setup, you’d likely want something else.

Maybe Bazzite? It’s basically built for gaming, but doesn’t default to Steam’s Big Picture mode like SteamOS does. It comes with Nvidia drivers pre-installed, which is a big sticking point for lots of people; many have found and/or lost religion while trying to install Nvidia drivers on Linux, so having them ready out-of-the-box is a big selling point. And you can choose which desktop environment you’d prefer when installing it; I’d suggest KDE if you’re familiar with Windows, or Gnome if you prefer MacOS. It’s immutable, which is, to put it simply, controversial. Some people love it, because it means you won’t accidentally destroy your OS. But others find it limiting, because they enjoy being able to go elbow-deep in their OS config.

Debian and Ubuntu have been seemless, I suspect you'll find the same on all the other relatives.

Any Linux OS with Steam installed, but yes most likely the same on SteamOS.

My experience with games on Steam:

• 75% of games: Click to Download, click Play, nothing more needed.
• 20% of games will work, might need to select a special Proton version or put in one command.
• 4% of games need tweaks specific to that game, to enable multiplayer, get around certain crashes etc., OR the game works fine but multiplayer doesn't work at all due to anticheat set to block Wine users.
• 1% of games don't work at all due to either anticheat, DRM or another problem.

Workshop works just fine, 3rd party mods will often work, but then you'll need to get into the weeds of Wine to properly set it up, since installers are separate to the game.