The first thing to note is that this post will be filled with spoilers. Considering what the subject matter is, though, that shouldn't be surprising.
Not just in videogames, most forms of media follow the typical beginning, middle, ending format. And while the majority of the time is spent in the build-up to the end, the ending itself is often the most memorable part of any narrative. Could anyone forget Snape killed Dumbledore, or when Fran and Peter flew away from the mall in the helicopter? These are two very different endings, one heartbreaking and one encouraging (despite Stephen's death).
Gaming itself is just now realizing that not every game has to end with the world being saved. Sure, there are cliff-hangers to make you buy the sequel (see: Halo 2), and games that have endings for sequels that will never come to pass (Advent Rising, I'm looking at you), but by and large the player is left with a sense of satisfaction and accomplishment because what they did mattered.
Dreamfall, sequel to the PC adventure game The Longest Journey, has what is considered one of the most controversial endings in videogames. It was meant to be a cliff-hanger to get people to buy the sequel (which I am STILL waiting for), but unlike Halo 2 it left on a low note as opposed to a high one.
The game starts and ends with Zoe, the main heroine of the game, in a coma as she “tells” the player in a disembodied voice her story. I'm delving too deeply into it, but Dreamfall has one of the most mature and thought-provoking storylines centered around a theme of losing faith. The final moments are littered with obvious bait for a sequel, but what is probably the most depressing parts of the finale to the game is the character Faith.
Faith was a little girl who had been leading Zoe, the main character, through her transitions between the two worlds of Dreamfall. Through the story you learn about Faith's back story and it is truly heartbreaking. Then comes a scene in gaming that takes the death of Aerith in FFVII and makes it look like a soap opera:
The only other game I can think of that ended with a little girl's death was Persona 4, and that was the bad ending that I'd achieved on my first play through.
Speaking of Aerith, there is another ending that, while not as mature and thought provoking as Faith's demise, instead relies entirely on the player's attachment to a character. While everyone knew the ending to Crisis Core before ever putting the UMD into the PSP, nobody knew just how well done it would be.
Whereas Cloud, the protagonist in the main game of the FFVII compilation series, was introverted and and eventually came to rely on others (in a completely different way from Squall in FFVIII, I might add), Zack immediately started out likable. He was your stereotypical teenager character: loud, overly confident, etc. He wanted little more than to become a hero. While the game didn't delve into Zack's past, it did a very good job showing his growth and maturity as he realized everything he had taken for granted in the beginning parts of the game was little more than a house of cards in a tornado.
As everything fell apart around him and the heroes (Sephiroth, Genesis, and even Angeal) that had inspired him fell from grace, rather than wallow in self-pity like the main FF characters of the PSone era, Zack adapted while staying cheerful and optimistic.
And after the final boss battle with Genesis, I settled back to watch the cutscene ending. Then came the biggest surprise of the game: when Zack and Cloud were found by Shinra, the battle screen kicked in. The normal rocking battle music was replaced by a sad violin and guitar, and no matter how hard Zack fought, every enemy taken down was replaced by another one.
Watching the much-maligned (and deservedly so) DMW break down as Zack began to lose his memories, it did more than any cutscene ever could, because the player had been with him when those memories were made. Seeing Zack slow down and drag his sword after you had run out of healing magic and potions in an attempt to delay what was inevitably coming.
Partaking in Zack's death gave the player a feeling of failure. The euphoria of victory from the defeat of Genesis is completely erased, leaving one vulnerable to what was next to come:
While I won't argue that every game needs to be depressing in the end, and I very much like saving the day, if that's all you ever do then it will eventually lose its impact. If everything ended on a high note, then the medium of gaming would become stale. That's why novels need Rhett Butler to give up and leave Scarlett, and movies have to have their Spirits Within. (I may be the only person in existence that liked the movie, but it was a depressing ending.)