There was a point in time where first person video games couldn't make their minds up and so games came with the option to have the y-axis inverted. Moving the mouse up would make the PC look down and vice versa.
It's because of joysticks and typical flight controls. Pushing forward goes down and pulling backwards is "pulling up".
Joysticks rules for a long time before the mouse came out. Home computers came standard with joystick ports.
Keyboard controls followed this convention and when mouse controls came into FPS games this was the first instinct... Moving the mouse "forward" looks down.
I hate when games DON'T have the option. In FPS non-inverted makes more sense, but in 3rd person games if I can't invert the camera if just feels unplayable.
Still a setting in any game worth caring about. I still prefer inverted in some cases.
Something like a mounted turret makes more sense inverted if you think of the mouse as an analog of your hand. Moving the handles down would move the tip of the barrel up. This analogy could easily extend to a two handed rifle or even a hand gun if your mental reference is the back of the gun, the handle