Depends on the material, just like books or films.
Uncharted, sure, loved the ending - was smiling and felt well rewarded for the time I'd invested in the (unusually rounded) characters (for a videogame).
On the other hand I do enjoy the tortured musings of a Silent Hill or the depths of a Deus Ex (first one, second was so dumb by comparison I worried what had happened to the brains of the creators between instalments).
Its a different experience, but if you play through ICO (one of the best games ever for characters) you will be rewarded by a bittersweet the very uplifting ending.
Not to sound arrogant - but for real depth of 'intellectual musing' I'd still turn to a book or certain films... but I hope to see some games (not all of course) try and use the medium to deliver similar content.
Of course games (and to an extent even films) are still very much in their infancy next to books in this regard.
Try to be reasonable... its easier than you think...
