The fact that Bigfoot is the supreme master of everything. Need somebody to do your gardening? Bigfoot! need a flower arrangement? Bigfoot. Need home repairs? Bigfoot! need a makeover? Bigfoot. Need a robot? Bigfoot!
A Sims 2 mod allows you to catch the burglar and freaking take him on!
Sims 3 allows you to do it without mods, if your Sim has the "Brave" or "Can apprehend burglars" traits (the latter being a hidden trait given to Sims who get far enough in Law Enforcement).
The Sims 3
With Sims 3 Pets, having a Simbot ride a unicorn.
Better yet, having a fairy or sunscreen-wearing vampire ride a unicorn. Sparkles everywhere!
The later stages of the firefighter profession have your sim pull one CMoA after another
The ghost hunter profession
The horse death animation. The Grim Reaper gets on the horse, strikes a pose, and rides off into a portal.
Getting Pangu's Axe, without cheating. The scene itself is just amazing.
"Dragon Valley" (All of it) if you're willing to pay the DLC, you get a neighbourhood descended from medieval culture, filled with Elven people and you can get pet Dragons that you can use to play with, freeze your needs, or set fire to your enemies. If you get the premium set, your Sim can practice archery.
In the Pets expansions, if a Sim's pet loves them enough, they have a chance to save them from the Grim Reaper.
The Sims 4
A meta example in The Sims 4's "gender update", which allows for transgender and gender non-conforming Sims for the first time in the series. Every single piece of clothing, accessory and hairstyle was converted to fit the other physical frame, not as part of an expansion, but as a free update. Of course in things like trousers this wasn't complicated, but upper body modifications were much tougher.
The Sims 4 game pack Strangerville features a plotline that ends with your Sim saving three Sims from a brain-warping infection, recruiting them to save the town, and then fighting a giant plant monster to kill it and save Strangerville. Yes, there is a proper boss fight in a Sims game.
Reaching the highest level of celebrity in the "Get Famous" expansion earns you the right to place your own star on the Walk of Fame in Starlight Boulevard, complete with cheering fans!