Start with the application requirements and work back to the device.
Ok here's an application I used them for. 6 channel "cable headend in a box" for simulating old style TV channels on vintage TV sets. Requirement is 6 composite video streams, priced at <$30/channel including storage. Low power is very important, as there's 6 of them, very small size also important, they need to fit in a rack of mini RF modulators. Must have readily available free software that will do what I want to do, I'm not a software developer and didn't want to waste time developing something. Low cost is the absolute top priority after being capable of doing the basic job.
Here's another application, Plex media streaming client, max cost $50, must fit inside an existing ~6"x4" enclosure from an obsolete streaming device. Onboard network, WiFi and HDMI a must, as is availability of a SPDIF output device for audio and ability to connect IR remote, max average power consumption 5W, must be silent and fanless, and again I don't want to develop any software, I just want to throw something together and have it work.
Another one: Mini desktop arcade game cabinet, must be able to run MAME and interface to small TFT or OLED display, max power consumption ~5W, "off the shelf" FOSS, I don't want to mess around with trying to develop software for it.
Home Assistant server, max cost $75 all in, including memory, storage and zigbee, average power consumption 5W max. Silent and fanless.
Interface between Davis weather station and internet, max cost $50, must have WiFi and run Weewx or other off the shelf FOSS, USB required for interface to weather console. (I'm using a Pi Zero W)
Connect to internet and parse RSS streams, displaying them on scrolling LED message board, off the shelf commodity hardware, mostly off the shelf free software, compact enough to fit behind the LED panels. Ability to either SSH or web interface to configure the device and update software remotely.
These are all real world applications either I or a personal friend are using RPis for, if you think you can come up with a solution for each of these that is equal or cheaper in cost, power consumption, physical space than a RPi then please clue us in. You might notice that in all of these applications, cost is the absolute #1 priority (these are all hobby projects), followed by power consumption and physical size. The Pi's have been at least as reliable for me as any other computing devices I own so that's total a non issue. The fact that you've seen someone use a RPi for something an AVR could do is irrelelvant, I've seen someone hammer in a nail with a pair of pliers, that doesn't mean pliers are useless junk. You're not going to stream MPG video on an AVR, and I think you'd be hard pressed to do any of these applications I've mentioned on any microcontroller without spending a great deal more time and or money and for what reason? A religious aversion to the RPi? To avoid imagined problems? How many RPis have you actually personally used that have had SD card failures? You seem to be really fixated on industrial/embedded/enterprise stuff where reliability is paramount, cost is no object and support is expensive and not grasp that there are millions of applications with different priorities. Decent quality SD cards work just fine as mass storage, I've had enough of them doing just that for long enough that it is a significant enough sample size for me.
Please do point me to all these bargain priced RPis flooding ebay because I can't find them, all I see is stuff like this
https://www.ebay.com/itm/313677239921 which is an old model that is selling for more than the price of a brand new one, probably because the Pi3 has some advantages like lower power consumption.
Or this one
https://www.ebay.com/itm/324799462677 that's what I'd consider a bargain at that price but it has 6 days left, it will go for more.
Model 2 is a bit cheaper, but this is like 8 years old.
https://www.ebay.com/itm/274932788422