The RP2040 is probably overkill for many projects but the pricing makes it very attractive for almost everything except where power and size are a huge factor. Many less capable MCUs are more than twice as expensive.
It can also replace an FPGA in certain cases where you need handle low latency I/O by using RP2040's Programmable IO [0]. (Not that it's the only microcontroller with this of kind abilities, there are others.)
The PIO is absolutely awesome, it's 100% the killer app for RP2040. Coupled with a second core to babysit PIO if needed, it actually competes with $15 microcontrollers in I/O heavy workloads (which is a huge proportion of all microcontroller workloads).
[0]: https://www.cnx-software.com/2021/01/27/a-closer-look-at-ras...