Nested AD group access not working when nested more than 2 levels deep?
Sorry I don't get it. You write:

On my database I have added the group RGP-EA_PROD-RW as a user login and given it db_datareader and db_datawriter roles

Okay. So that means RGP-SparxEA-RW ought to be able to login and do stuff.
User1 is not a member of RGP-EA_PROD-RW so why do you expect for User1 to be able to login?

User1 is a member of BGP-FunctionalAnalyst who is a member of RGP-SparxEA-RW, who is a member of RGP-EA_PROD-RW. So yeah, User1 is ultimately a member of RGP-EA_PROD-RW.

MYDOMAIN\User1 suggests when you login you the username needs to be prefixed with a real domain.  There is a weirdness that the groups that work are all Domain Local Security Groups.  Maybe the Global Security Group only exists in Azure - not on the local domain controllers. Next step is to query group membership with VBscript or Powershell and confirm whether the group membership is what you think it is.
Standard practice is to make local groups inherit from global groups. In your case it's the other way around.