Note that your subject line and the question text are not identical.
While the import is very similar the meanings are subtly different.
An accustomed English speaker would get much the same message from either.
The former is arguably more "euphonic" even if, perhaps, apparently less grammatically correct.
- How common are major cities not built by abundant water?
... refers to the city.
It queries the commonness (ie lack of rarity) of cities which belong to the class "major cities not built by water".
Properly, the city has to still exist to qualify.
But the query
- How common is it that major cities are not built by abundant water?
... refers to the act of building.
It queries the commonness of the building of cities besides abundant water, where the cities belong to the class "major cities".
Here, no time period is specified for the building. The time scope of " ... are not built ..." will depend on the understanding of those involved. To a modern town planner it may relate only to current city construction practice. To an archaeologist it may logically include cities built since Sumer onwards.
While both meanings are liable to be interchangeable in a recipients mind, and while it is hard to sensibly imagine a city which belongs in one set and not in another, the two questions are not identical and, on analysis ask quite different things.
You could argue that the question re building cities could be answered with examples from all of human history, whereas the question re the existence of cities might be deemed to apply only to cities which currently exist. To make such a fine distinction is liable to be substantially more pedantic than would be justified in most cases, but may be something that specialists might do in informal conversation.
Pedantic example to illustrate the point: Babylon, in what is now Iraq, was a major city and was built by water. For a long while it existed, having been built by water. Then for a long while it no longer existed. While some would argue that it now again exists, it is no longer a major city.
FWIW cities that never qualify for either condition would be rare. If Macchu Pichu ever qualified as a major city then it would be a fine example.