Source: used to work for a direct Uber competitor (not in the US)
Drivers and platforms definitely don't want too intoxicated passengers. Reasonably drunk? Sure. Completely high/passed out/etc? Nope. It's just too much of a mess to handle. There is indeed a vomiting fee, but it's more used as a deterrent for the users: it does not cover the actual cleaning + lost rides on a busy Saturday night...
Bit of anecdata: sexual intercourse in the car is also not ok, and gets you banned. Yes, it happens. Drivers don't like it.
Ironically, part of the reason traditional taxi networks enjoyed their local monopolies in many cities was to compensate them for being considered part of public transport infrastructure, and forced to do the things that's not in their best interest (but is in the best interest of citizens).
