I'm guessing that lots of Picos will see use as enhanced GPIOs for Pi "motherships," which provide backhaul to multiple wired-in Picos.
In fact I have a project which could use a decidedly non-wireless USB GPIO, for use on an airplane imaging system.
It can replace many IC and will make a very good addition to ESP32 which lacks pins for many projects. And, sometimes you want external chip so output is not affected by ESP32 reboots or freezes.
If you need wifi, by all means, go integrated. But not everybody does.
On the other hand I often find myself missing USB-related features on the ESP32, such as being able to act as HID device or even a USB host. The ESP32-S2 fixes that, although software support for that is still relatively immature.
For instance: https://launch.arduino.cc/nano-rp2040
It's also clearly going to have a great developer community building up around it, which has great value in itself.
It's also great value as a "easy to integrate cheap module" which is what they initially pitched it as.
Of course these days many applications need a wireless interface (wifi/BLE) and you can get competitive parts at similar prices to the RP2040 (ESP32 or a BLE MCU).
Either way it's no longer cost effective.
The quality of the documentation is phenomenal. Very competent people clearly put serious effort into making it great.
Another stand out is the Programmable I/O feature. Basically, you have eight separate, programmable state machines that can manipulate I/O pins and do DMA. This lets you offload bit banging from the main cores. Details are in Chapter 3 of the C API docs: https://datasheets.raspberrypi.org/pico/raspberry-pi-pico-c-...
I'm a total amateur when it comes to embedded development, so I have no idea how the RP2040 sits in the competitive landscape.