Storm King's Thunder
Basically, without giving real spoilers, giants are running amok and the players get sucked into what's happening. They have to figure it out and I love the twists and turns that happen along the way. I already know how I would modify and streamline it if I were to be the DM to run it... but the core of the adventure is very interesting.
I especially love that there are a few key paths you can take the story down that are fully fleshed out.
Overall, if I ever do pull the trigger on getting a D&D group together and I don't feel up to trying out a homebrew campaign I would definitely want to run Storm King's Thunder.