And some game recommendations by me to add on to the post:
Taiji
A 2D puzzle game where you slowly unravel how to solve each different element of the puzzles, eventually culminating in a massive puzzle gauntlet. Basically identical in concept and execution to The Witness, but still very much its own unique and fun game.
The Golden Idol
A puzzle game where each level you must examine a scene to figure out exactly what happened, eventually piecing together the full story over several levels. Don't let the art style put you off, it's an incredibly well done game. Most similar to Return of the Obra Dinn in concept.
Stories: The Path of Destinies
an action RPG with a branching choice-driven storyline, but not every story has a happy ending... You'll piece together the true story over multiple playthroughs and eventually find the one true path. It wasn't a particularly life-changing game but it was still a lot of fun and worth checking out if it sounds interesting!
I tend to not get into these types of games because the progression is often a time-limited loop, and a lot of the time you have to go to the same place or do the same thing multiple times before you can finish it because those are places or actions that don't persist through a loop.
Similarly, games setup like Mario 64 where every level is intended to be played 5 or 6 times to get all the stars instead of just having it designed in a way that you can get everything in one go if you're good enough.
Obra Dinn, you're just investigating a memory and doing detective work. You might notice a slip of paper, or broken glass, that helps you with the next step.
Tunic is more about unlocking pages, that you can then remembering that "ah to open up a red door, you have to do a little dance".
Both games, you can absolutely play them in a linear path without repeating the level over and over (like your example of Mario 64).