>>423864
I'd say first to consider a classic game that focus predominately on exploration, adventure, and self-motivation, as discussed here:
>>422895
But since we're talking quests:
>Standard Fantasy RPG trope quests
Nothing beats a quest for a specific magic item that somebody else (or maybe a lot of people) also wants. The reward of the quest is presented upfront. Because the motivational conceit is I want shit, you can have an interesting array of rivals as antagonists - anything from a greedy sorcerer coveting a ring of power, to a noble knight aiming to get a staff of resurrection to bring back his wife, and all the colors in between. It also adds time as a factor, which means there's an actual chance of failure; and that failure, in turn, can mean more things to do. From a meta-perspective, device-driven quests can be applied at almost any level of play, or even extended over entire campaigns.
>Your favorite quests you've played/run
In tabletop? A wizard used magic to capture and imprison the human half the party (which consisted of PCs and henchmen), so our non-human remainder had to infiltrate through his defense of cyclops guards. It was a mess, people died, but fun.
In general? Hard to say, but probably the side-quests in Morrowind and (the better ones in) Oblivion. But it's difficult to say, because the best "quests" there aren't really quests, so much as shit that you pick up in rumors and follow on your own. Things like finding a letter and a key on a corpse in a tomb, and following the directions from place to place.
>Your favorite quests with twists and unexpected outcomes
Dunno. I've pretty much always seen them coming. The best "twist" ending I know of, though, is the end of the film the Usual Suspects.
For running a game, the first NPC I introduced was a bad guy, and I made it very obvious he was a bad guy, up until the point that they eventually decided he wasn't a bad guy, before immediatPost too long. Click here to view the full text.