It’s a terrible design for anything else, because it can barely get beyond LEO without in-orbit refuelling.
None of the competing rockets (e.g. New Glenn) resemble Starship in the slightest, because none of them are intended to fly to Mars.
Given SpaceX's business, it seems safe to assume that Starlink was a design goal with at least a similar priority to the Mars goal.
Another use case it'd work fabulous for would be a LEO space hotel business.
Finally, it's also a great rocket for any use case that involves returning large masses, even if the return is from higher than LEO. Yes, it'll be a thirsty beast requiring many refuelling trips, but the tyranny of the rocket equation makes it hard to do any better. If you want to return dozens of tons from the moon or elsewhere you'd be hard pressed to do better than Starship.
Then again, Musk is also big on reusing components as much as possible, so he might have opposed multiple fuels on principle.
You need much larger tanks, so the mass advantage is pretty much completely eliminated. Hydrogen engines generally have much lower thrust for a given size too. Falcon9 or Starship style staging is infeasible with a hydrogen second stage. Rockets that use hydrogen for their second stage separate a lot higher and faster than Falcon9/Starship to make up for this reduced thrust. This makes Falcon/Starship style 1st stage recovery impossible.
Hydrogen would be great for a 3rd stage. If you want it to be recoverable, design a third stage that fits within the Starship enclosure. This would be a fabulous way to do small BEO missions without requiring a whole bunch of refueling.
For most purposes the customer does not care one iota about the booster, they are interested in the cost per kg to get where they are going. For low orbit hydrolox imposes more handling nightmare costs than it saves in amount of rocket, it is not the fuel of choice unless you're trying to impress.
(Now, things change considerably when you looking at deep space. But methalox or even kerolox fueled in orbit still beats hydrolox fueled on the ground. And hydrolox is much less storable--your rocket costs weight, necessary to reach orbit but once you're up there a smaller engine means less wasted mass. The only advantage to a bigger engine is Oberth and that is only truly relevant if you either care about time (Apollo took an inefficient path for this reason), or because you are going to carry velocity into deep space. Look at the flight path of the Webb. The booster flew higher than the maximum efficiency path because the deep space stage was puny. It wasn't powerful enough to circularize normally, the telescope fell back quite a bit before the engine had built up enough velocity to stay up. But it was worth wasting some energy on that in order to not lift as much engine away from the Earth.)
Falcon Heavy put a car in a trans-Martian orbit, and Musk has been about Starship-like things going to Mars before SpaceX managed to launch the Falcon 1, let alone them getting a chance to bid for the return-to-Moon mission.
But the Artemis mission isn't really about doing things sensibly, it's about pork barrels. You can tell by looking at the wild disparity between the vehicles, where there's this complex process to put a handful of astronauts on a space station and transfer them to a landing vehicle… but the Lunar Gateway is smaller than Starship, and I think small enough you could fit all the parts of the Lunar Gateway inside the payload volume of one Starship.
If the USA wants to go to the moon for its own sake, they could do it cheapest by just paying SpaceX for a ride, not all the other contractors.
Settlement on Mars is out of one gravity well into another, so it's not clear if it's the best first location of a extraterrestrial human territory - Moon might be easier and more reasonable.
So the camp is split between Moon and Mars, and Musk has to be on Mars.
There is zero chance of building a self-sustaining base on either within the next fifty years, and probably within the next century.
It's not a freight problem, it's an ecology problem. Designing a life support system that is stable and self-correcting and isn't in danger of running out of some essential raw material or element isn't just an unsolved problem, it's a barely considered problem.
Ironically - or perhaps not - it would be much easier to create a self-sustaining population of machines on Mars and/or the Moon than any project that relies on incredibly complex and messy human biochemistry.
I don't think that's even ironic. It's the only viable path.
It should be like: robots keep 3D printing and launching giant capsule parts into L1/L2, which are to be robotically welded with captured asteroid inside so that the inside can be filled with all sorts of minimum viable tools until it's good enough to host life, and then interested life on Earth can choose to inhabit them.
We are not going to be welding space sailboats in an Apollo suits on Lunar surface and taking breaks on space prefab shacks. That just is not going to work.
Earth science closed loop ecologists since the 60s would like a word with you...
The entire point of Biosphere 2 was to run a closed system for long enough to discover unexpected causes of failure.
Aka science.
Mars is at best a long term goal for Starship, and far more likely to be just a nice story that Musk uses to motivate his engineers and investors.