Fallout 4 is the one that jumps to my mind. The beginning is really cool and it sets up this really interesting story, and then you step outside and it's the usual Fallout slog...

Most recently, Uncharted 4. Still haven't finished it, chapter 20 was a drag, way too much fighting for my play style. It's quite a ways after the beginning at least.

Most games lose steam at some point. Easier to count games that stayed great until the end. Ico, Sotc, Dark souls, TW2 (not TW3), R&C Crack in time, Tlou.

Now talk about movies that lose steam after the beginning. Skyfall's opening was awesome, then zzzzzzzzzzz.

Destiny from recent memory. I might attempt to get into it again sometime though.

Borderlands, great at the start then it gets repetitive as hell, couldn't finish it

Deadlight. Bought it after the excellent demo, and the game turns to garbage right after the spot where the demo ends.