Ick, yuck. and puke.
Well, OK, when done with PROPER biz/enterprise grade gear....it "can" mostly work fine.
I run across it more with SOHO/residential grade crap, that doens't do NAT well. I'll start with the first thing...troubleshooting staff "work from home VPN" back in the day when VPN was relevant. VPN stacks do not like to be molested by NAT. If you double tag team the VPN...it often gets cranky. I'd be able to retire if I had a nickle for every "double NAT'd home setup" I had to reconfigure properly (aka BRIDGE the ISP gateway)...so the staff of my business client could VPN from home. Nutgears and DStink and Stinksys and TPStink and...(etc etc)...resi grade routers do not do it well, combined with testy VPN adapters....yeah, it's a mess. I just never see a legit reason for double NAT'ing...network can be setup properly better split up from the edge, if multiple internal networks are needed. Let the edge device drive that.
Heck I have done double-NAT before merely because it was easier than dealing with incompetence from other IT folks...
This one time I was dealing with this idiot vendor running a Laboratory-Information-Management-System (LIMS) from an AWS environment to which we peered via a Tunnel.
They crazy irony is that THEY were running a web-server, which needed to be reachable on the Public Internet and needed ME to map one of OUR pubic IP to their Private IP in AWS. I always thought their piss-poor planning shouldn't be our problem, but I went with it.
Basically how it would work would be a member of the public would hit the website, which would find our Palo Alto firewall by our Public IP address from a CIDR block AT&T routes to us, and the firewall would then do a
Destination NAT translation to re-write the packets to forward them to their webserver on the other end of the tunnel in AWS.
Of course this is where the story gets to double NAT network gymnastics... This idiot vendor I was on the phone with for much too long explaining that if said web-server answers, the network rules (i.e. routing table stuff) MUST forward the response back via the SAME tunnel, so it can go back to the damned Internet with proper un-NATING keeping sockets functional etc. and without asynchronous routing, incomplete TCP handshakes etc... had not the slightest clue how networking works.
Idiot vendor could NOT figure it out how to just point a default router back at the IP on MY end of the tunnel.
I gave up and did a source NAT translation, too! I re-wrote the source of ALL packets I was rewriting the destination on going to said webserver to make it appear ALL were sourced from the IP on my side of the tunnel, so their webserver would ALWAYS reply back through the tunnel as if my firewall was the only website user!
It worked great until one day they kept getting locked out when a developer changed some code where three bad password attempts locked an IP

. Being only one (1) IP it just sort of broke the entire application.
By then they had this new fellow who was like, "we looked at the logs and ALL the IPs are the same. WTF is going on?" I was able to work with someone intelligent enough to make that observation.
The conversation went something like, "A: Well, yeah this one time in IT special olympics network camp, I threw my arms up in the air and said **** it..." In like 5 minutes new fellow was able to configure his end to do proper routing, and I removed the source translation... problem solved.