It all depends on how the aircraft is configured and therefore pretty much which airline. If FC is in the nose section, I prefer UD to main deck. If it's BC in the nose, I prefer main deck, especially row 1. No one goes up to row 1 except to get to their seats or to the closet (not an issue for most of the flight). Really nice and quiet up there. On the upper deck on 747-400s, I hate sitting in an aisle seat as I can feel the vibrations of heavy walkers when they walk past my seat.

As for the privacy and exclusiveness. UD on the old 747s - the 100s, 200s and SPs - will feel more exclusive than any 747-400 and -300 ever will.

So, I like UD in WBC on NW 742s (old FC seats vs. BC seats on the main deck). On NW 744s, I prefer main deck especially row 1.

Similarly on certain 744s operated by O8, the nose section is preferred over the UD: nose section may have old NH FC seats while the UD has old NH BC seats with really cramp BC seats. Doesn't matter on the airline's old SQ 744s.

Not when it isn't a spiral staircase.