The two key elements to the perfect apple pie are flavour and texture. You want a balance of sweet and tart, without mushiness or mealiness. That's why Golden Delicious and Braeburn apples, according to J. Kenji Lopez-Alt of Serious Eats, have the best flavour and texture for baking. Image from J. Kenji Lopez-Alt.
Kenji tested 10 varieties of apples to see how their flavour and texture fared when baked in a pie. He looked for apples that came out tender but not mushy, and weren't overwhelmingly sweet or sour. Golden Delicious turned out to have a balance of sweet and tart, while not softening too much when baked. Braeburns had a pear-like flavour and were fairly firm after baking. All the other varieties either didn't meet the flavour or texture standards.