It rather depends on the genre. Noir, for example, tends to favour the ambiguous, the unhappy, the morally bleak. There may be an element of "happy" (yay, they caught the killer!) but the protagonist rarely comes out unscathed (physically, emotionally, morally etc). Take the Maltese Falcon as a typical example: case solved, but the woman is lost, and the treasure worthless. Not particularly "happy". The ending really should follow the tone of the rest of the story or else it will come across as rather jarring.
So just because "most" stories have happy endings, it would seem sensible, to me at least, to avoid genres that one considers "usually always have happy endings". There will be exceptions, of course. There are always exceptions.
The latter part of that sentence pretty much excludes it from being about "real people", I'd have thought. I mean, I've yet to meet a "real person" who has superpowers. Have you?

If you mean that they are fundamentally
human stories, exploring the
human condition, etc, then sure. And a good story in that regard is a good story. The issue then is whether you can get past the conceit, the setting, etc. I know people who just can't handle anything sci-fi, even if literally just the setting. E.g. set Romeo & Juliet in a sci-fi setting and it's a big no-no, even if the language is unaltered from the original.
As for unhappy superhero: try
Watchmen by Alan Moore. The graphic novel is slightly different in the end to the movie, slightly more ambiguous, but certainly not "happy ending" stuff.
Joker is good, as well. I haven't seen the sequel yet, but imagine it's about as nihilistic and as uncomfortable as the first.
V for Vendetta - not "happy" but does offer some "hope" for the future.
And then there's
The Boys series - which is excellent TV viewing for anyone who wants a dark, cynical, and deeply unsettling view of the superhero genre!
As for how Superman can fly?
Well, not many might know, but he began unable to fly! In his earliest incarnation he could leap over tall buildings, and great distances, but he couldn't actually fly! And the leaping was due to his strength. Seems obvious enough.
Then he began to fly, so they retconned everything. Now it's through the means of hand-wavium particles that are immune to any plot-inconsistency potentiality. Quite possibly due to microtubules.