When overlapping windows do (and don't) make sense
The more I think about it, the more I think that there are a number of conditions that need to hold in order for overlapping windows to make sense (or at least to be attractive). I also think that these conditions are not necessarily all that prevalent, which may go a long way towards explaining why tabs and maximized windows are so popular.
First, small displays lead to maximized windows (and thus tabs) because there isn't enough space to go around. This isn't just resolution, it is also the physical size of the display, since you need windows that are big enough to read no matter how many (or few) pixels are involved.
Once you have physical space and enough pixels to do something useful with it, you run into a paradox:
The best way to use overlapping windows is to arrange them so that they don't overlap.
It's rare that you need to see only part of a window, which is the only time that significant overlap is useful; at other times you might as well iconify the mostly covered up windows. So to be happy with overlapping windows, you need to have enough space and to set up an organizing scheme so that they don't usually overlap. If you don't, overlapping windows will just irritate you (especially if your window system auto-places them in bad ways), and I suspect that this is one reason that many people don't like and don't use overlapping windows very much.
Or in short: the best way to use overlapping windows is to tile them most of the time, and overlap them only occasionally when you really need it.
Unfortunately, setting up an organizing scheme for this is a lot of work. Not only do you have to figure out where you want things to go and how to fit everything in but you have to carefully make sure that all of your windows are the right size and stay the right size, often without very good tools for it.
(I have an entire set of infrastructure to make sure that my terminal windows come out right and for resizing things into the sizes that I have determined tile together the way I want them to. As a result I am fanatical about things like the width of my browser windows, because if they grow too big my tiling scheme breaks down.)