Compared to the ESP32-S3, the RP2040 doesn't have Wifi/BT, doesn't have an RGB display controller, has slower clocks, and has less RAM and Flash.
For most IoT devices the ESP32-S3 is overkill unless running a large display, and you could use the cheaper ESP32-C3, which has 1 core instead of 2, no RGB interface, and less GPIO. These run about $2 each for a solder-able module with built in antenna: https://www.digikey.com/short/cttthh0h
It could be an advantage if the rp2040 had a decent low power mode - then you could only turn on the wifi when you need it (eg data upload every few hours) and run for a year on battery. Alas it doesn't...
I think the main advantage (to some, in the current geopolitical climate) is that it's not Chinese.
The ESP series can disable wifi/bt entirely if needed, their low power sleep is pretty good. IIRC someone got a year or more out of a single 18650 cell doing very slow updates.
ESP32 doesn't have RP2040's PIO. It's just amazing, and can easily emulate RGB display controller or whatever you want it to. Even software HDMI with most CPU power still available. Thus bitbanging possibilities are endless, and this chip can sometimes replace an FPGA.
Pi Pico W boards also indeed have Wifi and recently bluetooth was enabled.
RP2040 also overclocks pretty easily, and people have pushed it even past 400 MHz. Wouldn't overclock in anything important, but for hobby projects — why not! It's about as fast per clock as dual core ESP32 (except for DIV & FPU math).
Both chips have their uses. For example ESP32 got HW FPU and integer division division, those can really help in some cases.
For most IoT devices the ESP32-S3 is overkill unless running a large display, and you could use the cheaper ESP32-C3, which has 1 core instead of 2, no RGB interface, and less GPIO. These run about $2 each for a solder-able module with built in antenna: https://www.digikey.com/short/cttthh0h
Or $6 for a dev board with USB built in: https://www.digikey.com/short/9rp1zbb3
I'd compare the RP2040 more to your classic STM32 arduino dev board, with the ESP series in another class above those.