In an interview with the devs at Rare about Sea Of Thieves, Eurogamer got the details behind the endgame plans and some of the different things you'll be able to do once you progress far enough. According to the article, the end goal is to become a legendary pirate, the kind they sing songs about and hold in infamy as some of the boldest adventurers and some of the blackest hearts. Apparently, the end game will have you bumping into some NPC pirates who have already met their demise but live on as spirits who challenge you to do as well as they did. The interview it worth a read, but only if you wish to be spoiled about what's to come. Here's a snippet from the piece below.
Inside is a secret pirate hideout, a place exclusively reserved for players who have obtained the pirate legend rank. There you'll find the Tavern of Legends, which is built into the remains of an ancient shipwreck. In the distance is a ship and a waterfall that leads out into the game world. It's all very Goonies. During a demo of the area, I saw ghostly pirate legends sat on chairs drinking grog. These are real players' pirates, lifted from Sea of Thieves' appearance at E3 2016, now immortalised in the game. By talking to these characters you learn hints of what's coming next to the game; whispers of a new trading company, perhaps. The secret pirate hideout looks like this: