The guy who made this video followed up with an in-depth post on the SMB camera system. This part blew my mind:
Most of the level design in SMB3 is horizontal and only two screens high. Most of the action occurs in the bottom half of each level with the camera locked vertically in place until Mario takes flight or climbs a vine. Because the levels are carefully crafted with these simple rules in mind the camera behavior appears to be much more complex than it actually is.