A couple of quibbles that no one else seems to have brought up. Ensign Tilly was not promoted to XO - she was assigned to the position of acting XO while remaining an Ensign. The designation of "acting" makes it clear she's just holding down the slot temporarily until Saru makes a more permanent selection, and makes it much more tolerable for those she's now in charge of despite the lower rank. It probably wouldn't have been tolerated (by the Admirals at Command) even in the lax standards of Starfleet, but even ADM Vance realized Saru had to pick from the tiny crew he brought to the future with him, so he let it go.

As for Ezri, again, remember that rank and position are only loosely connected, while rank and specialty (Counselor, science officer, engineer, navigator) are COMPLETELY unconnected. Ezri is a counselor because they wanted to make her distinct from Jadzia, and is an Ensign to highlight her inexperience both as a person and in dealing with a symbiote.

But yes, it does seem like the writers' familiarity comes and goes. I swear there are at least a few episodes (and I can't remember which series, because I'm alternating between three right now) where people refer to a Lt. Cdr. as "Lieutenant" instead of the proper term of address, "Commander."

Likewise mostly Enlisted people would be spending a few years at most and moving onto other careers in civilian life as most people don’t want to be in the military forever and if they do they become officers.

We see this bear out in Miles O’brien. He enlisted in starfleet rather than attending the academy. He climbed the ranks (mostly in backstory) and by the time we meet him in TNG he’s a chief petty officer, non-commissioned but officer nonetheless. When he accepts the promotion/transfer to DS9 he achieves the rank of senior chief petty officer.

Its hard to say for sure that the rest of the show is inaccurate (or otherwise) in this regard because the shows mainly focus on bridge officers. Many characters receive offscreen promotions throughout the shows and you have to pay close attention to their uniforms and rank insignia to catch it. But also, you have to bear in mind how narrow a view we get of any crew outside of like Voyager.

For example, the Enterprise-D, as a Galaxy-class starship, can have a crew complement of anywhere from 1,000 to 6,000, and TNG as a show overwhelmingly focuses on just 7 of them. “best” case that’s a view of about a half a percent of them, so it would be easy to draw nonsensical conclusions if you overly extrapolate from that small percentage. Maybe a lot of crewmen do actually retire after just a few years, it’s hard to say for sure since there is basically no evidence either way.

Basically everyone in the commissioned officer corps (ensign and above) attended the academy first in order to receive the officer commission. You would not go to officer academy in real life if you did not intend to dedicate your main career to being a military officer, and likewise, people who attend starfleet academy generally intend to dedicate their careers to starfleet. Every depiction of the Enterprise is that it is the federation flagship and so we can consider a station there as being highly sought after and likewise regarded, it represents a high potential for being a crowning point on anyone’s career, so it kinda makes sense that the core officers might be reluctant to trade in for an inferior assignment, even if it meant a bigger promotion. Another aspect of this is loyalty: most of the officers depicted across all of the shows are supposed to be stand-out talents among the federation, with solid leadership skills that clearly foster loyalty. Loyalty that can create a reluctance for too much change. We see this reflected in Riker’s long resistance to getting promoted off the Enterprise to Captain. He doesn’t want to, partly out of loyalty to Picard, but also partly out of loyalty to the officers that report to him, and by extension the rest of the crew, and partly out of the chance that his potential new command is simply not as cool as being the first officer of the Enterprise.

Voyager has different circumstances, of course: the crew complement is much smaller, and they are stranded, but Rick Berman also decided that field promotions would be unlikely in their circumstances since they have no real way to gain more crew

There would be fewer officers

Important to note that even among the enlisted ranks, the NCO corps begins just one rank above crewman. Similar to real life military there are only 3 ranks of non-officers

This is one of the reasons Discovery effectively promoted Tilly rapidly (all the way to being the XO for a little bit) because she was a pivotal part of the cast and needed screen time.

It's a testament to Mary Wiseman's acting that the writers sought to give Tilly more screen time. But the way they did it made my eyes roll through the roof. I did not for a second consider it believable that an Ensign, only a couple (?) of years out of the Academy, would be skyrocketed through the hierarchy to become the Chief Operating Officer of the ship.

Tilly's promotion was even more unbelievable than when Jed Bartlet made CJ Cregg his Chief of Staff, elevating her above her Toby Ziegler (whom she reported to) and Josh Lyman. I know why the writers did it - because Allison Janney was the best actor in a world-class ensemble, and they wanted to give her more to do. But it didn't ring true to me (even though there is an argument to be made that all the West Wing characters are so hyper-competent that reporting lines are mere formalities).

Sure, you could argue Starfleet has a history of promoting promising youngsters unbelievably fast - like when they gave that cocky repeat offender fresh out of school command of the most modern ship in the fleet simply as a reward for saving the world. But even so.

This actually goes to a related point about DIS and how the writers treated the rank of its main character. Discovery was notable in that it was the first show where the lead actor was also not the most senior member of the crew. As David Gerrold pointed out in The World of Star Trek (IIRC), there's a reason why the captain is the star of the show. Because in a crisis all decisions come back to that role. And the ability to make decisions is what makes for good characterisation (Hamlet notwithstanding). But if the command decisions, the decisions around which the plots and the drama pivot, are ultimately made by someone other than Michael Burnham, more senior than her and who can overrule her, what do you do then? Well, you make the Captain a baddie (like in season 1) or you find other ways of making the main character go against orders (which Burnham did repeatedly). Unfortunately that led to a backlash because the character, Mary Sue-like, was always proven to be correct whenever she went rogue.

I actually liked the fact that the lead character in DIS wasn't the CO. But - like Tilly's promotion - I just wish the writers had found a better way of exploring that dynamic. Having Burnham assume the traditional captain's seat in season 4 was - in some ways - an admission of defeat, but an understandable one.

Lower Decks of course pulls it off, but as a comedy the stakes are generally lower, and the fact that the main characters often don't know what's going on, or aren't in a position to decide how to shape events, is part of the gag.