The demand for SFHs will always exist - some people are willing to pay extra not to hear or smell their neighbors through the walls. We can meet that demand with new builds, and once pressure is relieved from current suburban areas, you can go back and start to incrementally redevelop those areas more efficiently as well.
There's a worthwhile discussion to be had on this point alone. I believe condos are so small and bad because of a confluence of two factors: (1) a lack of regulations around quality, (2) too many regulations around zoning, making vertical space scarce instead of abundant.
Factor (1) leads to a lack of privacy and noise because the builder wants to save on materials costs. Factor (2) leads to condos being too small because vertical "land" is so expensive when it should be cheap if restrictions didn't exist.
It should not be possible to hear your neighbors through the walls, if we use multiple layers of air gapped steel or concrete. Condos that achieve this exist. They're just not common because regulations don't enforce it.
Around here, I think there is definitely a market for bigger houses, because any house with 3K+ sqft sells for a butt ton and people are tripping over each other to fight for them. The new suburbs that are being built are getting a ton of demand and their prices have spiked in the past few years at a far greater rate than surrounding housing.