Hard agree, I have one and I love it. Its currently doing the VM work of what a £10k server did in 2012.
But if I want GPIO, and or battery powered things with linux, then the pi is the way forward still.
Anything else, and a pico/esp32 will do well.
Two separate platforms connected over USB is substantially more complicated, expensive, power hungry, and consumes a lot more space.
The value prop of SBCs is that they're compact and you can do low-level work in a single package. Connecting an RP2040 to a PC and writing software for both is the opposite end of the complexity spectrum.
It doesn't have to take up much space either: https://www.waveshare.com/product/rp2040-one.htm
However the best thing about the Rpi GPIO stack is the support. If I google "how do I do x with y on pi" I will get lots of resources, along with the official docs (the rp2040 docs are great by the way) and a bunch of hardware that is mostly plug and play.
You have the full Linux kernel and packages to play with, the GPIO stuff (if performance isn't a huge deal) can easily be controlled by simply writing to files, meaning that you can do quasi-embedded stuff in basically any language you want, or at least any language that has an ARM port to Linux, which is basically everything nowadays.
Still, I'm quite partial to the ESP32 with NodeMCU just because the power consumption is so ridiculously low and NodeMCU + Lua is pretty easy since it has full GC and gives you a node.js-style callbacks that I think are generally easier to work with than doing it myself in C. Most of my embedded projects have moved to the ESP32 as a result.
the rp2040w has wifi, micropython and a bunch of GPIO, all running at low hundreds of megs. Great for motor controllers and that sort of stuff. I use them a lot for the sort of things I would have used Pis for.
However thats because its python. If you want rust, or javascript, then the pi is probably still a good option (I know there are native ports, but they are not as common or easy. )
But if you want computer vision, or, can't be arsed with micro based programms, then pi is where its at.
The other type of application is home assistant. I have an N100 running HA in a VM. It would have been easier with all the home security stuff to have it running on a pi directly. (yes you can use esp32 as binary sensors, which is what I do, but its expensive to have things that aren't wifi.)
Another I have thought doing is ADS-B receiver mounted outside. It helps to put the receiver close to antenna so would put the SDR and Pi in enclosure, and power it from PoE. Microcontroller can't run the SDR. Micro PC is overkill and wouldn't work in enclosure. Doesn't use GPIO pins.
Linux would probably be very easy to built without needing firmware
I've also used GPIO on a Raspberry PI Zero W to build a Stratum 1 NTP server with nearly spooky accuracy with the PPS line.
Both things worked very well. They were compact, performant, used an inconsequential amount of power, and were very inexpensive.
And both things were very easy for me to implement, largely due to the tremendous amount of software available in the Linux-ey ecosystem.
If I were trying to bodge an MCU into performing these tasks without involving Linux, I'd probably have never gotten either of them done.
Yes - and at the same time, the RPi has gotten more expensive, rising from ~$25 circa 2013 to $60-$80 for the latest RPi 5. Neither price including power supply and SD card. Of course the RPi 5 has more cores, a faster clock speed, more RAM, and built-in wifi so you do get more for your money.
Once upon a time, you were looking at $25 for a Pi and $250 for intel. These days it's more like $80 vs $180.
The Raspberry Pi Zero 2W is also very interesting for the size and $15 price tag. I also liked the 3A but no one remembers that one.
They're older, but everyone else is still playing catch-up, and Raspberry Pi will produce them for a while. I do hope they have a proper $35 board to upgrade to in the future.
But if you specifically want to run Linux and have SPI and GPIO on the same chip then sure, the RPi will do that.
In some cases you want to avoid your programmers needing to know two designs, compiler toolchains etc - a microcontroller might push you towards using C, and perhaps all your other code is in Python and you'd prefer to keep everything in Python.
If you're making something like a high precision time server synchronized to GPS, you might want your GPIOs to trigger direct interrupts on the device with the ethernet port. Of course, IIRC the RPi has USB ethernet so it's not a good choice for a truly high precision time server.
Yesterday I received two Ryzen 9 mini gaming computers [1], one to replace my old rack mount server and one to be a home theater PC. Each cost about $400, and they are capable of emulating the PS3 and Xbox 360 smoothly (I don't really have any new games so I wasn't able to push the limit too much, but still emulating those consoles requires some horsepower!).
Maybe I'm just out of the loop (very likely), but $400 for a super low-power gaming computer feels insanely cheap to me. The server one in particular will pay for itself in about a year due to power savings alone compared to my rack mount server.
[1] Beelink SER6's for those interested.