Azumarill's line first appeared in the short Pikachu's vacation before the Pokémon movie, having scores of kids scour Kanto looking for this Fabled "Pikablu" said to be stronger than even Mewtwo. While Azumarill's first appearance definitely did not live up to the hype, several generations of generous buffs and mechanic changes is now having Azumarill live up to the legend, culminating in Azumarill being excellent for the first two Unrivaled raids with Charizard and Cinderace (albeit with wildly different sets).
Water Fairy is superb both defensively and offensively, leaving 3 weaknesses for twice as many resistances plus an immunity, and has no common resists among its STABs, leaving only a handful of Pokémon capable of taking Azumarill's Huge Power assaults. 100/80/80 Defenses across the board is incredible, leaving Azumarill difficult to take down in a single strike. Having STAB priority in Aqua Jet does do a bit to alleviate Azumarill's speed issues, and combines well with Belly Drum, turning Azumarill into a force of pure destruction.
So with all these excellent traits, why does Azumarill falter in VGC/Doubles? It is down to a very hostile environment. While the Intimidate Spam of Generation 8 is gone, Azumarill still has plenty to deal with. Bad matchups against popular Pokémon including Iron Bundle, Iron Hands (who has the same base Speed and guarantees a OHKO with Wild Charge while Azumarill needs to be running Life Orb/Choice Band to do the same with Play Rough), Dondozo, and Amoongus. And this is not even getting into the fact that most of Azumarill's favored targets (Armarouge, Arcanine, Volcarona, Iron Moth) often carry Grass/Water Tera's that Azumarill struggles against and can often Burn or outright OHKO Azumarill.
Dondozo is another huge problem for Azumarill, not only being a hard wall for Azumarill, but also competition for a team slot. While the two fill very different roles in Singles (Unaware Tank vs Bulky Sweeper), in VGC Dondozo often fulfills the same role as a Bulky Sweeper, with it and Tatsugiri being a potent win condition, something Azumarill cannot claim due to Belly Drum being difficult to setup and maintain in the current VGC. Even as a Perish Song user, Azumarill is outclassed by Scream Tail (who boasts better defenses AND speed alongside a fantastic support movepool) and Fluttermane (who fullfills the slot as an offensive Perish Song user, is faster, and has a good matchup against Ghost types that otherwise cause the strategy to falter).