Top Tier
Curse of Strahd -- Definitely #1 for me as well, both as a player and a DM.
Storm King's Thunder -- Could be #2 for me, again both as a player and a DM.
Tomb of Annihilation -- Had a lot of fun DMing this one. Wouldn't mind running it again but without the death curse (at least at first) so more of the side quest locations can be explored.
Scourge of the Sword Coast (one of the D&D Next Sundering adventures) -- Although the story itself is a bit pedestrian, it has some of the best adventure locations I've ever seen / used. I've run this adventure twice. My only complaint is that its climactic ending ended up being the tacked-on beginning of its sequel, Dead in Thay. (That part was also sadly left out of the updated version that appears in Tales from the Yawning Portal.)
Lost Mine of Phandelver -- I suppose I'd better include this up here too. It's not perfect, but it is definitely a solid starting adventure. I've only run it once but would happily run it again.
Middle Tier
Tyranny of Dragons -- Yes, it has problems, but I had a helluva lot of fun running it, and my players had a helluva lot of fun playing it. Maybe you just need the right DM / group?
Princes of the Apocalypse -- I've never run it as a campaign, but I've poached a good chunk of the adventure locations for other campaigns, and I've used the "Trouble in Red Larch" beginning adventure to start two campaigns so far. I'd say I like that segment as much as, if not even more so than, Lost Mine of Phandelver.
Tales from the Yawning Portal -- I've run some of the adventures from this book, and they were fun, although I think they could have done with less of a literal translation. Some have too much treasure, others just feel a bit dated, and the giants are too big to fit in their own lairs going by the map scales.
Ghosts of Saltmarsh -- I've only run two adventures from this book, but it looks like a lot of fun, and I like what they've done with the town of Saltmarsh and with the "build your own nautical adventures" section at the back.
Legacy of the Crystal Shard / Murder in Baldur's Gate -- I love these for the gazetteers. The adventures are pretty good, too, although I feel both should have been for higher level PCs, and both could have done with more descriptions / maps for the various encounters. They can be a bit bare bones at times.
Lower Tier
Out of the Abyss -- While I've used a few set pieces (like "Neverlight Grove" and "Gravenhollow") to good effect in other campaigns, I think overall it's a bit messy and disjoined as a campaign. Possibly too depressing as well.
Waterdeep: Dragon Heist -- Utter trash. Worst 5e adventure by far. (I've gone into plenty of detail elsewhere, so I shan't repeat myself here.)
Waterdeep: Dungeon of the Mad Mage -- Not that interested in running a megadungeon campaign, and most of the levels just aren't interesting enough to me to want to use them as standalone dungeons. Also, Skullport feels like a waste of space given how empty it is.
Unranked
Baldur's Gate: Descent into Avernus -- Haven't read it yet, though I've read that it has some issues (like not having enough content in the second half).
"Orrery of the Wanderer" -- The adventure from the Acquisitions Inc book looks like a lot of fun. In fact, I would say it even looks like it's better written than many of the adventures named above. However, I have not yet had a chance to test it out in play (and may not get a chance for some time).
Dragon of Icespire Peak -- I bought the Essentials Kit but haven't had a chance to do much more than glance through it. I'd be keen to run this in combination with LMoP sometime, though.
