Why does the 'X Rotation' in After Effects seem to move the camera up and down the scene when traditionally the X axis is horizontal? And obviously this means the 'Y Rotation' rotates the scene on the horizontal axis? I feel like this could have been made so much simpler by making these rotations line up with traditional axes.
Think about the camera on a tripod. X is horizontal, and Y is vertical. Rotating the vertical axis pans the camera left and right, and changing the horizontal angle tilts the camera up and down. Rotating on the Z axis would then rotate the camera on the lens axis. It's all normal.
It's the same with a 3D layer. There is no difference in the move. You are just thinking about the camera in the wrong way.
It's the same in Cinema 4D (and most other 3D apps). Blender flips the axis around, but there is an easy way to migrate camera settings from Blender to After Effects so the cameras match.
As a general note, there is no 'traditional' standard when it comes to 3D behavior and XYZ order. Some software use Z as up, others Y. There can also be differences in rotation order and some software allow you to define/change this.
I belive the reason why you are confused in this particular instance is the expectation that you are viewing (through) a camera, but what the rotation control is really doing is rotating the object itself. It's not a POV camera like in a video game for example where y axis of a game controller would indeed relate to the camera looking/rotating up and down.
It's best visualized if you look at the camera in a custom view in 3D and look at the gizmo while it's selected. You are virtually grabbing the red X axis and twisting it with your hand causing it to rotate 'up/down' respectively.