A timely question given the season, and an important one. Pick the right apple for an apple upside-down cake and you get a firm flavorful apples inside a sweet, moist (yet still firm) cake. Pick the wrong one and you get applesauce in a sodden mess.
In general you’re looking for a firm apple that is also sweet. My go-to, because they’re so easy to find in the US, is the Golden delicious. Lately Gala apples have become popular for baking, though I have a hard time understanding why since they lose an awful lot of flavor in the oven. Other decent choices are Jonathans and Jonagolds, Winesaps and Newton Pippins. Best of all, if you can find them, are Red Romes, also known as Rome Beauties, probably the preeminent baking apple.
Avoid at all costs Red Delicious and McIntosh or anything labeled a “cooking apple” since these types break down to mush with heat. The Granny Smith, though it’s the first apple that pops into most peoples’ minds when you talk about firm apples, is actually a rather so-so choice, for reasons I mentioned below.