Witcher 3's main game ending was extremely good, IMHO. Epic battle, well-earned apocalypse that they've been building up toward for three games, and I got something fairly happy in the excellent postlude. I actually got a rather sad ending for Blood & Wine but it was *still* great because it was so well handled.
NeiR: Automata messed with the whole concept. Technically, there's 26 endings but there's really only one true ending which came after the credits rolled for the last time.
I agree regarding endings that completely mangle a game's systems to try and make it more epic. Risen was hurt bad by that, as was the first Arkham game. I think it's a pretty rare mistake, though.
What can be fun are games that twist the mechanics just a bit. Something that's probably really shocking but, once you think about it a little, you can overcome it with some small changes to your normal strategies. Sacrifice did that nicely.
(Sacrifice also had the funniest end credits ever.)