Regardless of service branch, US warrant officers are intermediate between E-9 and O-1.
WO-1 is a special warrant issued by the Secretary of the appropriate service, CW-2 through CW-5 are warrants issued by the President, hence are effectively commissioned officers.
The distinction is that the warrant officer is not in the command line: a warrant officer is a technical leader, providing expertise in highly specialised, career class fields.
The reason that the Air Force discontinued their warrant officers is two-fold: the supermajority of jobs in the Air Force, enlisted or otherwise, are technically oriented, and the creation of permanent E-7 through E-9 grades.
In the Army, Marine Corps and Navy, there are much larger enlisted forces than in the Air Force, and in these services there are lots of non-technical jobs.
As for the CW-5 grade: The original plans called for six grades of warrant officer, paralleling the commissioned grades O-1 through O-6. CW-5 relieves the inevitable career compression experienced by the limited number of promotions and salary jumps allowed warrant officers.
In the Army, warrant officers are in abundance as helicopter pilots and CID investigators. They also serve in certain highly technical fields. They might be present in cryptography and medical support also. The original Army warrant officers worked in explosives and ordnance.
In the Marine Corps, they have warrant grades WO-1 through CW-5. In the Navy and Coast Guard, they only have grades 2 through 5 (no WO-1). I have not a freaking clue as to what they do, though I suspect that the Marine Corps warrant officers have some involvement in ordnance.
In the UK services, the original warrant officers were the sailors who ran the ships staffed by army officers in the early Royal Navy. Warrant officers in the UK services are merged in with the senior sergeant grades, and are viewed as senior NCOs, as opposed to the officer status of warrant officers in the US services.