For me -- and I'm not completely sure why -- I really value having a single disk volume to contend with. I like seeing one icon, backing up one volume, and not having to think through (or train my family) where files should be saved.
I do, too, but here's my biggest reason, which rarely gets mentioned in these threads: I have Apple's 2 TB Apple iCloud backup plan. For files to be backed up to iCloud, they have to be in the Documents folder. The Documents folder has to be on the system drive -- it can't be on an external drive. Files in Apple Music and Photos libraries don't count toward the iCloud 2 TB limit. All told, I have about 900 GB of stuff (excluding ripped DVDs, which I store on an external HDD, which I clone to another HDD for backup). Of that 900 GB, about 550 GB is in my Documents folder. The rest consists of apps, system files, and my Music and Photos libraries.
In order to make use of my 2 TB iCloud backup plan, I need a system drive capable of holding my apps, system files, and Documents folder. I could move my Music and Photos libraries to an external drive, which would mean that a 1 TB system drive would be fine. I was in the market for a 27" iMac a few months ago, and I was on a limited budget. The 1 TB SSD models were pricey, and I didn't want a 1 TB Fusion drive model because of the small SSD component. The 2 TB Fusion drive was the sweet spot for me in terms of price, giving me room to grow, and taking advantage of my iCloud plan. On the Refurbished store, I found a 2019 27" i5 model with a 2 TB Fusion drive and 8 GB of RAM for $1,700. I spent another $100 for 16 GB of RAM from Crucial. So for $1,800, I have a computer that's a big step up from both my 2012 i7 Mac mini with 16 GB of RAM and my 2015 13" i5 MacBook Pro with 8 GB of RAM.
As for the reliability of Fusion drives, several years ago, I replaced the stock HDDs with Fusion drives in both my wife's and my 2012 Mac minis. Both have received heavy use and are still going strong. One of my good friends has been using iMacs professionally (which means every day) since the first iMac model came out. His first ones had HDDs, and his last two have had Fusion drives. He's never had a drive fail. He keeps an iMac until it becomes annoyingly slow for the work he does, and then he upgrades to the newest model. So while a Fusion drive is statistically more likely to fail than a pure SSD -- and I have no idea what percentage of iMac Fusion drives fail versus what percentage of iMac SSDs fail during the useful life of the computer -- I had no hesitation buying an iMac with a Fusion drive, because my experience with Fusion drives has been good. If the Fusion drive fails during my iMac's useful lifespan, depending on costs at the time, I'll either pay a professional to replace it (probably with a 2 TB SSD) or boot from an external 2 TB SSD in a Thunderbolt 3 enclosure.
As for the minor speed advantage of SSDs over Fusion drives, I think too many people get hung up on specs rather than real-world usage (much like the pixel peepers in the photography forums I visit). If you're using a computer professionally in situations where time is money, and you really do need the fastest drive-access times possible, then I'm guessing you can justify the extra cost of an SSD. I use a Windows PC at work, but I prefer a Mac for my multimedia hobbies. For what I do, my new Fusion-drive iMac is much faster than my 2012 Mac mini or my 2015 MBP. Obviously, the processor, the amount of RAM, and the GPU also factor into the equation, but I doubt that getting an SSD rather than a Fusion drive would have made my iMac feel significantly faster.
Sorry for the long rant! If you have the money, then, by all means, go for an SSD over a Fusion drive. I would have, but I couldn't justify it financially. But I have to disagree with people who tell others to absolutely avoid Fusion drives because they're obsolete (in what way?), they're old technology (so what?), they're likely to fail ("statistically more likely" isn't the same as "likely"), or they're slow (do you need the slight extra speed of an SSD, and would you even notice it for what you do?). As SSDs continue to drop in price, I'm sure there will come a point when Apple stops offering Fusion drives -- but, for now, they're still a good option for people who want more drive space at a low cost.