I actually quite like Leopard and have not been experiencing problems with it, as some people seem to have. Then again, I always wipe the hard drive and do a completely clean install with major point releases. It forces me to do a bit of (disk) housekeeping as well!
As with most OS X releases, I thought I'd upgrade once I could see there were no disastrous issues with it and I didn't really feel it would dramatically change things. I like the interface changes and have come to like the 3D Dock (although I initially didn't). Time Machine has been useful and I've fortunately never needed to call upon it so far. I do like Stacks and use it for shortcuts to my Applications and particular documents.
Much to my surprise, Spaces has been a major plus for me. I figured it was something I'd never use, but it has changed the way I work. I used to use the Hide command all the time, but now I keep different applications, grouped by theme (some start up automatically in pre-set Spaces), in different Spaces. Having a multi-button mouse is very useful when working with Spaces and Exposé!
There are lots of other nice touches too, but, yes, it is ultimately an upgrade to OS X rather than a radically different operating system. As far as downsides go... a few of my games don't work under Leopard, and there were some teething troubles with other programs until the developers released patches. The only glitch that annoys me to this day is that, when using the Save As dialog box, sometimes it is set to Desktop as the default, and yet does not actually show the Desktop, but somewhere else. Clicking on Desktop in the navigation sidebar on the left, does not bring up the Desktop and I have to click somewhere else (e.g. Documents, Applications, whatever), and then come back to the Desktop. It only does this sometimes, though, and I haven't identified the pattern yet!