Silent Hill and Silent Hill 3 - both games revolve around a cult that is trying to bring about the rebirth of their deity. You play as someone who finds themselves in the town of Silent Hill in search of a person. You solve puzzles, battle monsters, and navigate the town… and then it ends with you fighting a god.
Oh It Takes Two is a good one! Played it with my partner a while back and had a great time!