Scientist! Not only do I love the Area 51 vibe of the lab in the middle of the desert, this career also unlocks new potions and unique inventions that your sim can use in their everyday life as you rise through the ranks, whereas most other careers only give you decor items, outfits, and/or alternate versions of objects you can already buy and use as it is, with no real motivation to get promoted other than the paycheck.
I like the objective-based gameplay of active careers in general, even if it becomes repetitive after a while. If only the other active careers were as good as Scientist! Detective would be more enjoyable if it didn't force you to go through several loading screens in a single work day, Actor has you wait several days in-between gigs (provided you even remembered to look for the next gig the moment your sim came home from the previous one), and Doctor is annoying in many ways, what with doctors not staying with their patients, too many patients at the hospital at once and the game berating you for not being able to treat them all, diagnoses being a guessing game, co-workers doing nothing, etc. The vet career is better in that regard, although it also tends to spawn too many patients for my sims to handle.
Other than that, I enjoy archaeology. I realise it's not a career per se, but gets pretty close to a freelancer or stay-at-home career once you reach level 9 of the archaeology skill and allow museums to send you artefacts for authentication, with the occasional trip to Selvadorada to find more artefacts on your own.