Tragedies are severely underrated in our fiction industry. The goodguys win so often in our stories that we usually go into the climax fully expecting them to no matter how outmatched they are. I think it's because writers are immature and can't handle the thought of the power fantasy being broken. The power fantasy that goodguys can always win if they try hard enough. And actually, I do think there's some truth to that. I think being good gives you a certain resolve that makes you more powerful than you would be if you were evil. But this effects has limits. Let's be honest with ourselves. Sometimes the fight is unwinnable. And it can still be fruitful to write stories about that, to show the courage of the heroes who sacrifice their lives just to put up a dignified fight, knowing that it's the only thing worth doing. Other times the fight is winnable, but the heroes are unlucky. Luck is an inevitable and massive part of real life conflict. The goodguys can't always get lucky. And they can't always overcome bad luck. Other times the heroes do have a legitimate way to force a win, but they make a mistake. Real people - even the best ones - aren't perfectly intelligent, and are bound to occasionally make a strategic oversight. We need stories about that too. And finally, there are times when the goodguys fail because of a moral flaw. Just because someone is on Team Good doesn't mean they're pure good on the inside; they're likely to occasionally waver in their resolve, and that can be a very satisfying way for the heroes to lose as well. (A certain Doki Doki Literature Club mod that I've reviewed is an excellent example of this.)

And I confess, I'm guilty of contributing to this. Almost all of my WIPs have ultimately triumphant endings. But that's because I came up with the framework for most of them before I decided that we need more tragedies in our fiction. I'm going to change my ways.
So now let's talk about different kinds of tragedies.

The failure.

This is the most obvious kind: the goodguys try, and they lose. This is the kind I discussed above. It can be due to any of those reasons: a mistake, bad luck, a sin, or it was impossible from the start. If the goodguys fail because of a legitimate strategic mistake, that can be a good setup for shame, almost as good as the sin. How would you feel if you were a rebel leader against an oppressive regime and you messed up the tactics of a battle and got all your brave freedom fighters killed? This can be especially powerful if the protagonist themself is still alive, and even gets to live a relatively comfortable life afterward.

The pyrrhic victory.

This is the least extreme kind: the goodguys technically win, but their victory comes at a tremendous cost. Was it even worth it? Even if it was, they can't find it in them to rejoice, and they'll never be able to forget the pain of what they've lost... or perhaps the pain of what other innocents have lost because of their actions.

A diminutive version of this trope can be used to good effect even in a story that is ultimately a triumph, such as my novel Pillars of Life. In that one, the heroes manage to overthrow the Voren, and there's no denying it was a victory, but nearly all of them die doing it. They were severely outmatched to start, so it would have been unrealistic if all of the main characters made it. It would have hurt the legitimacy of the story.

The fall from grace.

In this type, the protagonist doesn't necessarily fail to stop the villain, but rather, they become the villain. An example is the Star Wars prequel trilogy. However, Star Wars softens this tragedy by having him redeem himself in the original trilogy.

It doesn't even have to be a complete fall. I have some ideas for a future project that ends with the heroes turning away from their duty in a particular situation, and the forces of good pay heavily for it, but they are still ultimately on the side of good. The remainder of their lives will likely be spent trying to atone for their sin.

Well, I guess I'm out of stuff to say now. I hope I've given you the inspiration to go write a tragedy!