Granny Smith is ubiquitous. It's often difficult to get a more sour apple than a Granny Smith. Pink Lady is less sour IIRC. McIntosh or Braeburn are definitely less sour.

If you're in the Midwest (especially MN, WI, Iowa, etc) you can get Haralson, which are kinda like Granny Smith but more just straight sour.

Haralson is probably my favorite. But disclaimer, I also like eating straight lemons, so ... yea. Many people will use Haralson only for baking.

I hear that in Europe they have a few types that are more sour, idk.