So I totally geeked out today and decided to wait in line at my local Apple Store for a copy of Leopard. I got there about an hour before the store opened for the launch, and ended up about 20th in line. I was in the first group of 50 or so let into the store, so I got clapped at by the employees and first crack at the limited Leopard T-shirts.
It took about 45 minutes or so to perform an upgrade install of Leopard on my 20" aluminum 2.4GHz iMac; the installer is a lot simpler than the Tiger installer, with fewer screens to click through before the actual installation begins. After the machine rebooted when the upgrade was done, it was all business -- no opening animation like you get with Tiger, it just takes you to the Desktop and opens a registration window (which is easy to skip). It didn't add any new icons to the Dock or desktop, and the apps I have set to autolaunch at login started up like usual. Some people have said that Leopard feels faster than Tiger; I haven't really noticed a difference (but then again, Tiger on a 2.4GHz Core 2 Duo with 2GB RAM is pretty fast on its own). I'll get a better chance to test this when I do a clean install of it on my 1.5GHz 12" PowerBook G4.
Some people don't like the new "glass shelf" in the Dock; personally, I don't mind it, but also wouldn't mind being able to switch back to the old, translucent backdrop. The new translucent menubar is slick, but something I wish was defeatable. Sitting at the Desktop, it's nice that it's unobtrusive. With an application open, though, the translucency feels out of place if you have a window pushed up against the menubar -- the menubar and window don't match, and makes the window look like it's just floating there. What's also interesting is that Apple finally did away with rounding the upper corners of the menubar; they're square now.
Stacks is useful for folders that you only have a few items in; if you have more than about 40 items, it'll only display some of them. I wish Stacks was defeatable too, because I like to keep the Applications folder in the Dock for those apps I don't use often enough to put in the Dock, and with Stacks I only see some of the apps I have installed. Under Tiger, holding down on the folder made it spring open a list of the contents that I could scroll through. Also, Stacks picks the icon of the first item in the folder to represent the folder in the Dock; instead of looking like a folder, the Applications folder shortcut in the Dock now sports Address Book's icon.
I was really excited about Time Machine until I read that it'll only work with a directly-connected hard drive, or through a network to a share on another machine running Leopard (client or server). I was hoping to use my NAS to house my Time Machine backups, but now have to either attach an external drive, or devote an entire computer as a file server.
Overall, Leopard seems worthwhile, and I think a lot of the interface complaints I have will either be addressed by Apple or through third-party software.