Have you played The Wolf Among Us? It's a Telltale adventure game, so in terms of gameplay it's more like an interactive graphic novel, but the story is great, and the detective work and interrogations are really immersive.

Perhaps a point & click adventure would be a good fit? I’ve played and quite enjoyed The Blackwell Legacy and I’ve heard good things about other games by Wadjet Eye.

LA Noire was really fun on the steamdeck. Low key wish rockstar would make #2 instead of all that GTA RDR stuff..

The Return of the Obra Dinn is by far the best detective game I have ever played.

I wouldn't rate L.A. Noire too highly, as it's a weird hybrid between detective game and GTA.

What rubbed me the wrong way was the illusion of choice in side quests (roadside police work) where it seems like it's always the worst possible outcome. The story, especially the choices my character made (without me having a say in it), wasn't to my taste either.

Tales of the Neon Sea is a nice cyberpunk adventure based detective title.
Gemini Rue is similar but more dark and in the tone of Blade Runner.
Sherlock Holmes: The Devil's Daughter is another really great title, although its more of an overarching story told during/through the various cases.
Call of Cthulhu is a detective story set in Lovecraftian horror. Really great endings.
The Sinking City is an open world detective story set in Lovecraftian horror. Gameplay outside of the detective aspect is not great but does its job.

A lot of detective games are mostly point and click adventure games, which generally are older games or more indie style. That said there are a few I can think of:

The Gabriel Knight games have a lot of good detective elements, including some good puzzle solving. https://www.gog.com/en/game/gabriel_knight_sins_of_the_fathers

Chicken Police has a really neat interrogation mechanic and a fantastic noir setting. Its a bit silly all things considered, but the detective elements are fun. I've not beaten the game, but the time I've put into its fun. https://www.gog.com/en/game/chicken_police

In a similar vein, Backbone is a noir detective game set in an animal setting. The detective mechanics feel weaker, and the game seems to force you on to the critical path pretty hard, but its still been fun so far. https://www.gog.com/en/game/backbone

Finally, this is a horror game, but unraveling the mystery is fun and the game doesn't railroad you on the critical path too hard. Simulacra is a game about a guy who receives a mysterious cell phone and he is using all the tools available on the phone to uncover the identity of the owner. Short and engrossing. https://www.gog.com/en/game/simulacra

Another one that I think is fun (though don't get the sequel) is Don't Feed the Monkies. This is more like Simulacra and is less detective game and more about experiencing the stories presented and unraveling a light mystery. Still fun and fits in the detective theme: https://www.gog.com/en/game/do_not_feed_the_monkeys

The Sexy Brutale is a really interesting game with more fantastical elements but there are some tricky puzzles to figure out with the clues the game gives you: https://www.gog.com/en/game/the_sexy_brutale

Last one, and one I've not played any of. Case of the Golden Idol apparently has multiple critical paths. Supposed to be really good. https://www.gog.com/en/game/the_case_of_the_golden_idol

Most of these are on sale now.