In RBY Aero is a gimmick. Give it Rest and it walls most Snorlax, and of course its Sky Attack, while requiring a turn of charge-up, can OHKO pretty much any non-Rock type with a Critical Hit (which, considering its Speed, is very likely). Outside of that it doesn't really have much going for it.
In GSC Aero is barely usable. Again it's more a specialized Snorlax wall with a Curse + ST set, or I guess it could make an okay attacker since it outspeeds the Electrics and hits them super-effectively, though as an attacker it fails to hit Snorlax, Suicune, or pretty much anything else hard enough. It's mediocre at best no matter what it's doing.
In RSE Choice Band exists so Aero can actually do something with its Attack and Speed despite having a low-powered STAB. Plus it also gets Rock Slide for hilarious flinching action and Rock Head to avoid killing itself with its strongest move, Double-Edge. Hooray! Speedy, Spikes-immune, and the best STAB in the game (Rock Slide) are Aero's biggest selling points.
In DPP it was the most reliable Stealth Rock suicide lead in an even more prominent lead metagame than even that found in RBY. I mean it's kind of a waste of a Pokemon to just throw it away solely for rocks unless you are playing hyper offense in which case consistent Rocks are super-important to get the kills you need, but there you go, a whole genre of team loves Aero suicide leads. Other than that it still has decent enough attacking sets I guess, but really it's famous for its lead role.
In a nutshell, Aero struggled for relevance in RBY and GSC because its STABs were too weak to match the offensive role its stats were meant for, but its defenses weren't good enough to let it be the defensive Pokemon its typing wanted it to be. Then ADV came along and gave it CB so it could overcome its low-powered STABs. Then comes DPP and its lead-centered metagame, and with access to Taunt, Stealth Rocks, and a Speed stat that eclipses all Stealth Rock users, Aero found a niche there. Overall, Aero was at its best in ADV, where it was actually a relevant offensive threat capable of bulldozing teams rather than the niche Pokemon it was in DPP or the absolute gimmick it was in RBY/GSC.