So I'll turn the question back like this: what do you think are nice places to live that have demonstrated effective solutions to affordability and mental health problems at scale in a relatively dense city with high property prices from high demand?
In my experience, the way this is "fixed" in a city like Phoenix is a combination of: "be able to sprawl outward further and further without yet getting completely blocked by geography so there's always somewhere to put new construction to keep costs down" and "have inclement weather that pushes the homeless out so we don't have to be the bad guys ourselves as much" - but these are basically the opposite of the most common proposed "if only the government could get it together" fixes for SF: denser construction and confinement. Those tools are likely necessary for a city like SF, but it's not like there are Republican cities showing how to get it done.
Neighbors are all friendly, well behaved kids roam the street unsupervised, it feels the same way it did 30 years ago here. It's deep blue territory. The weather is terrible and the Red Sox have gone off the rails, but you can't have everything.
The average is not as meaningful here because there are places all along the scale (except in Boston, again, kind of crazy there, but there are commutable suburbs with fairly reasonable prices).
No place is going to have the same diversity, fun, or nice weather like San Francisco. Certainly not in New England. That why it sounds like studios are going for $2500 or more there and I have a 3bed2br house for less than half that here.
The cool thing is I'm 15 minutes from downtown Worcester, 45-60 to Boston and Providence, and 2.5 hours to NYC. I find that I get out in one city or another about as often as people who live there, but am also able to take more trips internationally because I'm not spending a fortune on housing. But if you're the kind of person who really is taking full advantage of what a place like San Francisco has to offer then it probably does make sense to live there.
Lots of the Great Lakes Region. I live in a liberal area where the average house price is ~150-200k and I rent a 2 bedroom, ~1000 sq. ft. apartment in the middle of the city within walking distance to things for $950.
Though I also think you may benefit from a bit of the weather being inhospitable to most of the poor and homeless.
Also what is the diversity like?
Diversity will vary a lot. The manufacturing cities are pretty diverse: My city is about 60 percent white and 40 percent POC.
They do lean more towards 'working class' liberal than 'professional' liberal. More 'rah rah unions' and less Ibram Kendi. Though if you're in a university city or capital, both are decently represented. If you meant other than racial, I haven't had a problem being gay in any of those areas since the mid-00s and a lot of those cities have small but thriving immigrant populations and the cities are small enough that there is political diversity coming in from the burbs/rural areas.
I feel like the areas are more diverse than the big big cities I have lived in. (Boston, Seattle, Vancouver).
Hint: all of these places are solidly purple (but voted blue in 2020 so I counted them). It's like having actual political competition is a good thing!