I see posts on here pointing how terrible the Spartans we're at war, and how ridiculous media portrayals like 300 are, all the time. So who among the ancient Greek city-states actually *were* good at war, and what made them so successful?

I believe you may have overcorrected - while the media portrayals are ridiculous and the modern pop-cultural misunderstanding of Sparta is overblown, but calling them 'terrible' is also off the mark. Even our own u/Iphikrates, who never will pass up a chance to smash the popular conception of Sparta, still pays them respect when it is due. One of his posts includes an observation about those times where we know about a Greek force that broke and ran before it ever actually made contact with the enemy. Those panicking forces all have something in common - they're always facing Spartans.

Here are thus some previous posts on this matter:

The major post on the Spartans and whether they deserve their reputation;
On the poleis and their methods of warfare;
For completion, the bit about the Spartans making other city-state forces panic before they ever clash comes from this analysis of Leuktra.