Whether or not people use drugs or alcohol when they're not working shouldn't be relevant. If it leads to impairment in the workplace such that they cannot perform their job, then it becomes a problem.
Almost sounds like I have personal experience doesn't it?
I've lived all over as well. The Denver metro is about as friendly as Austin metro in my opinion. Texas as a whole is friendly because it's the south, but that pertains to non urban areas.
I do find people in the West to be poorly mannered but not unfriendly. Frankly, growing up in the South, people in other places have bad manners in general to me.
I think it's important to know that companies have different cultures, and your experience of Austin or Denver can be heavily affected by that.
The federal government sure does care. You won't be getting a security clearance if you are a user. This cuts you off from over a million jobs.
It's not harmless either, particularly if you breath it. Even non-drug stuff like Lysol and candle smoke will mess up your lungs. The drug aspect is real too; we don't have the term "pothead" for no reason.
What does this mean? If you mean cannabis use, who cares? It's legal and it's harmless. If you are talking about opioid abuse or something else, that's a different story.
It sounds like you had some subjectively bad individual experiences because you mixed with people that you are not socially compatible with, but there's nothing here (brogrammers that don't share your hobbies? weed smokers?) that seems like an indictment of the entire city of Denver. Similarly, while your new job is great and you have found your tribe in Austin, your nerdy coworkers are but a very small part of the selfhood of ATX.