I have not played any of the Final Fantasy games so I have no reaction to the character's death. But the talk around it reminds me of the 1960s horror classic, "Psycho".
In that film, Janet Leigh's character started out as the one that audiences identified with as the heroine, the character people did not expect to die. When Norman Bates kills her, however, it shocked audiences to see the central character be killed. It was something that had almost never been done in movies back then, just like dystopian and sad endings were unheard of.
The death of Leigh's character meant the only character left in the movie for audiences to identify with was Norman Bates, which horrified the audiences even more, but they kept watching anyway. That's a big part of what made the film memorable and talked about for 50 years.
I knew Aeris/Aerith's death would be at the top of the list but it even has its own long section.