Some of the better examples of subclassing seem to usually be basically interface - implementation cases. Eg, python's unittest module, or XNA's Game class where you can hook up your onRender, onUpdate events and let the rest of the engine handle everything else.
I suspect that you're on to something here. Inheritance trees are often set up to reduce code duplication by moving common functionality into base classes, but in most cases only the leaf nodes are expected to be instantiated. There are exceptions, but they're more for convenience or compatibility than philosophy.
Consider Python's
queue module. LifoQueue and PriorityQueue inherit from Queue, but only because they can use the same interface and most of the same code. I'm guessing Queue was introduced first; otherwise, LifoQueue could have been named Stack, and they may both have inherited from some abstract ThreadSafeContainer class.
But considering the community, a better example may be DF's own items. We have an abstract item class with much of the code, with subclasses for certain groups of item types, such as a class for items that get constructed in a workshop, and another for body components. But actual items in the game are instances of the leaf nodes in this class hierarchy, like metal bars, or gems, or eggs, or cages. The base item class is the interface, and these leaf nodes are the implementations.
(Granted, some of the apparent implementation baffles me. I'd expect to find a class for wearable items, one for edible items, and one for containers, but there may be something about either the code or its history that makes the current system more sensible. Then again, I had a similar thought when looking at NetHack's code. I have never seen more switch statements in a single codebase.)
We can learn a lot here from how relational databases do things. With poorly planned OOP sometimes we invent overly-complex "solutions" that have very obvious flaws, which already have perfectly working solutions in the database world. You can implement the same patterns in your code.
Many of my projects lately have started from the perspective that each table gets its own class, responsible for most of the behavior logically associated with that data. That's not always ideal, but is certainly a convenient starting point. For classes without associated database tables, it's all about code reuse. If a class provides most of what I need, but isn't quite right, subclassing is always an option (as long as it's less work than modifying, re-implementing, or wrapping), even if the resulting class hierarchy doesn't look good from an architectural standpoint.