It depends on the game.
Typically I prefer normal, but there are some games where inverted camera just makes it easier, like games with full 3D exploration (flying/swimming). I do sometimes only invert the vertical for these, so I’m that maniac.
If you are an older gamer, you’re probably right that you picked it up in the early camera control games (ps1/2 for me), because changing camera function wasn’t an option and a -lot- of games inverted by default back then. I believe the logic was to treat your view camera like a real video camera, where you push the back to the left to point the camera lens to the right, but most people don’t have that type of experience so it’s less intuitive, which is why it’s less common now. I blame mostly Spyro flying challenges for my limited inversion use.
And like you, I struggle with games that don’t allow me to pick, which is most of the older games, even remastered 😭 it’s so hard to re-learn.