It varies so wildly between genres of games that I’d find it impossible to give a one size fits all kind of answer!
Metroidvanias for example - the sweet spot for me is definitely around 10 to 15 hours for a first playthrough, I found Hollow Knight started to drag on quite a bit towards the end, think my playtime was in the high 30s by the time I saw the credits.
RPGs - obviously this covers white a range of games these days but I could play some of the games in this genre indefinitely if the game had loads more meaningful content there I’d spend hundreds of hours till I’d seen it all, few examples being Skyrim, The Witcher 3, Pokémon (all the mainline ones).
Racing games - much like above, if the game has enough tracks, cars etc I’d definitely put the hours into it, despite them being one of my favourite genres of games they’re not my most played anymore. For example, WRC 9 with my wheel is absolutely amazing to play (I’ll probably get WRC 10 if it ever goes down as cheap as 9 did which was under £5!) but at the same time it’s not exactly a relaxing game to play in the evening to unwind