De acordo com a FR wikia, which quotes the novel Blackstaff Tower, Meloon Wardragon is a descendant of the woman part of whose spirit is bound to the weapon in question. Moreover, at some point (1479DR) he seems to have successfully retrieved the weapon from a stump, and she (the weapon) is known to stick to things with the strength of sovereign glue when she does not wish to be wielded by someone. So we can conclude that she used to be ok with Meloon in the past.

Their alignments are different: Meloon is listed NG, while she is LN, yet she seems to have been ok with Meloon for many years (W:DH is dated to around 1492 DR). Moreover, on page 210 of Waterdeep:Dragon Heist, there is a small subsection "Adjusted Game Statistics" that describes what happens when Meloon is "saved", we can read there that he can now attune to Azuredge, which means she is accepting him.

While it is up to you as the DM to rule however you prefer, I would interpret these data to mean that he will keep the weapon after the resolution of his condition.

Your guess seems correct

In addition to the information you posted, we have this little piece to work with...

Personality. Azuredge is sworn to protect Waterdeep, and it desires to be wielded by a law-abiding person willing to dedicate everything to the city’s defense. The weapon is patient and takes its time finding its ideal wielder.

Nós também sabemos que

Azuredge is actively rejecting Meloon (or, more accurately, the intellect devourer in Meloon's head).

Porque

Each day at dawn, Meloon engages in a telepathic contest of wills with his magic axe, Azuredge, before leaving his room at the Yawning Portal. The axe wants a new wielder, but Meloon refuses to part with it.

This is a dead-ringer for someone having a conflito with a sentient magic item. I'm not going to copy the entire chunk in here, but here's the important bit summarized.

If you lose your contest of wills with a magic item it can make demands of you, such as demanding you never part with it, pursue its goals, part with some item it hates, or--importantly--give the item to someone else. If you refuse, it can:

• Make it impossible for its wielder to attune to it.
• Suppress one or more of its activated properties.
• Attempt to take control of its wielder

Sabemos que

Meloon has lost at least one contest with the weapon, because his stat block says "Meloon wields Azuredge (see appendix A) but can’t attune to it, and thus gains none of its benefits."

As for the why of things, the book does not address this directly but it is likely a safe assumption that it is looking for a new owner because

it's old owner is effectively dead, and his body is being driven around by the minion of a horrible aberration who works with a crime lord. And said minion is trying to use Azuredge in service of said crime lord. See, Intellect devourers don't 'control' you. They eat your brain and replace it with themselves--"you" are no longer in there except on a very, very thin technicality (punt out the devourer then immediately hit the body with Regenerate can restore you without resurrection magic).

Suffice to say...this is pretty antithetical to the weapon's personality, and explains what is going on.

However, if you have a look below Meloon's statblock, you see this little bit of info (again, condensed because spoiler tags protest to being multiline)

If Meloon is killed and raised from the dead, his true self is restored and his statistics change as follows: [...] He can attune to Azuredge

Thus, it is reasonably clear that if the current state of affairs is resolved, the conflict is resolved as well, and Meloon can carry on wielding his axe. After all, if he can re-attune to the axe, it has stopped rejecting him. Naturally, as DM, you may opt to ignore this if you want someone else to end up with the axe. (Note: As a DM, I advise caution at handing Azuredge to a player long-term, especially at low level...that axe is a friggin monster).

The module doesn't answer directly, but it does say that Meloon can now attune to the weapon where he couldn't before (p210, "Adjusted Game Statistics"). I think it's strongly implied that the previous owner's condition was the reason for the weapon being displeased and refusing to attune (as discussed in the DMG rules about sentient items on p.216, under "Conflict"), so resolving that situation in an ultimately non-fatal way would clear the path for returning the weapon to him.

But that said, it's really up to the DM to decide if the weapon wants to go back to him. If the PCs want to give it back to him and/or the DM wants him to reclaim it, fine. If the DM wants to decide that Meloon is done with being a hero after his experience, then that's also valid. Or he could just want to hand off the axe to his rescuers, intending to go on being a hero, but maybe not with that specific weapon. Or the weapon could be looking for a new wielder even though it's allowing Meloon to attune to it again, like grudgingly going along with Meloon while keeping a watch for some likely hero. It's up to you.