Is death supposed to be part of difficulty -- a result of skill, a penalty for the DM thinking you did something stupid regardless of what you thought, or just blind chaos with no rhyme or reason because, dice?
Well... see below for a serious attempt at an answer.
I said once per campaign, but really I want to say as often as the fates make it happen. It's not the quantity of deaths that I think about, it's just that it be a present risk that, if we are foolish AND our luck is poor, we will die.
This, mostly.
If the party can't roll higher than a 5 and my dice are hot, falling back to regroup might be a good idea. If they just defeated 8 jub-jub birds and their tamed witherwhat, are at 40-50% of their health and resources, and find a similar group around the bend in the river, falling back to regroup might be a good idea. Pressing on with low resources when the dice are cold is a poor plan.
I am currently running The Wild Beyond the Witchlight, an adventure perhaps best known for the possibility of getting through without a single combat, much less character death. However, the PCs have been terrified of the prospect of starting a fight with the adventure's main antagonists.
...
I think noting the difference between death and defeat is important here. For many players, threat of defeat is just as motivating as threat of character death.
I concur. Since there is "easy" returning from the dead, character death tends to be only slightly worse than defeat. Sometimes, particularly with younger players (< 18-20 yrs), the desire to avoid defeat by risking death trying to get that "Hail Mary" play leads to more deaths than defeats.
Tough to say, for me. Some players have plots with serious "main character" energy, and losing them can be a major blow to campaign continuity. Other players are often oriented around more of a "side plot", and their loss isn't as much of a distraction.
Yes, I'll never* have a "chosen one" character in a game again. While character death is relatively rare in my games, it can happen. Just because
raise dead exists doesn't mean it is feasible or successful. The party/team always carries the A-plot, everyone always has their own B-plot. If the team decides they want a different A-plot, well and good. But that's a team decision. The B-plots follow the individual characters, and are disposable if the character leaves/dies. Which happens sometimes because the player moves away, quits the game, is too busy, &c.
See that's the thing.... There's all this talk of real possibility of death... but what does that actually mean? A more lethal game...but what does that actually mean? I get the impression many want the illusion of these things as opposed to the reality... but don't want to state it outright.
Not the
illusion but the
feeling. And that's tricky.
The thing about RPGs that I love whole-heartedly is the blend of intention, logic, and passion that they allow. You can have "easy" encounters (the 7th level fighter, ranger, and paladin gleefully leaping into the fray against a platoon of orcs) that let the players feel like badasses. "Medium" encounters requiring some forethought and planning but are otherwise straightforward (crossing the rickety rope bridge by sending the rogue out first, the fighter holding a rope tied to their waist, and the rest of the party looking our for flyers or other surprises) where there is obvious risk but yield a sense of accomplishment at the end. "Hard" encounters, however, promote that "heart in your throat" feeling that yields to excitement and accomplishment when the team has worked together to win. "Win" being defined as achieving the objective and not losing the stakes of the game. These stakes being character death, social standing, wealth, or whatever it is.
This isn't an illusion. The end is not fore-ordained. There is real risk (gamewise), and this risk is mitigated by various means of preparation, forethought, learning means of adaption to the unforeseen, and hot dice. The risk has to be real to have the drama, the excitement, and the ultimate rush of victory or catharsis of failure. How often are or should the stakes be lost? In actuality, I think "rarely" is the best answer for most campaigns. It could happen, but if it does it should a memorable occasion. Even if it is a "random" death, the banality of it could be cathartic or philosophic, depending on the table.
This is hard. This is why premade adventures exist, and why there are so few "great ones". Especially since not every table has the same level of risk tolerance, identical stakes, or level of satisfaction for different types of victories or successes. Or, even loss tolerance for that matter.
Depending on how many beers are drank during session.
Like IRL, people take dumb actions with their PCs with more beer
Remind me to tell you sometime about the gathering of heroes at the storm giant's castle, and the... altered/drunk... spellcaster.

That wasn't tragic or dramatic, but wholly comedic.
* If there's one thing I've learned in health care, "always" and "never" aren't.