We have a lot of problems in my county with copper pipes leaking. There are entire plumbing companies dedicated to finding the leaks and repairing them. There are still more companies dedicated to repiping.
I know of developments where they epoxy coated all the piping because they were getting so many leaks.
You can read about the huge class action lawsuits going on involving the local water utility (who is being blamed for water chemistry causing the issues. They point fingers elsewhere, obviously).
I’ve seen a copper pipe where in the middle of the pipe there is a pinhole leak on the top side of the pipe (too far for turbulence, can’t be cause by sediment).
If you want the most durable, I would think it would be either pex-a or perhaps 316 stainless steel (which is a valid code compliant option). But no one really knows, and it’s probably water chemistry dependent. It’s being argued in the courts now (for whatever that’s worth).
Stainless steel is awesome! Unfortunately the connections are not so easy. Stainless steel-to-brass threaded connections are probably safe under most circumstances but are dubious per the code. Push-to-connect fittings often use little stainless teeth to hold the tubing in, and this may work less well when the pipe is nearly as hard as the teeth. (John Guest makes special fittings for stainless steel tubing.) You can’t sweat stainless steel pipes.
Obviously everyone should plumb their houses with swagelok fittings! :)
MegaPress and ProPress are a couple of popular brands.
The also have an advantage that they avoid "hot work" in places where it leads to greater regulation / stringent safety precautions.
On the other hand, I lived in a rowhouse built during a brief window in which it was OK with the code to run PVC from the main into the house. Whatever PVC is good for, it doesn't handle shearing force well. Essentially all of the townhouses needed repairs (a copper "pigtail" through the wall).
Sadly, even today, most plumbers will show up to a job site with petroleum-based flux that most certainly does not meet B813. It can stay in pipes for years, slowly leaching chemicals that do their job: weakening the protective oxide layer in the pipe. Also it tastes and smells terrible.
PEX with compression fittings is the closest thing to copper in terms of durability, but exists because of labor savings. Appropriately installed copper is the most durable interior water service and will comfortably outlast the life of the structure.