A great game, particularly with extra custom content and mods from the internet community.
First off: you're gonna want a killer system if you want those massive skyscraper districts that go on for an entire large city map. That's very important to this game. I have a 2 GHz Pentium 4 with a gigabyte of RAM and my performance on even medium cities full of skyscrapers is very laggy -- imagine what it is on a large city, four times the area of a medium one.
Anyways, if you have the system or don't have the desire for massive skyscraper districts (remember, you can also link together many smaller cities and have interconnected demand and so on, so this isn't that big of a problem, if you're willing to work with it a bit), this game is totally awesome. It's an amazing sequel to the first three games of the series, although SimCity 3000 Unlimited was a lot of fun in its own ways. I'd recommend having both if you can afford it; if not, then go for this one.
Anyways, I rated the difficulty as "Hard" -- even though you can adjust city difficulty settings with the addition of Rush Hour (before Rush Hour, the game was often extremely frustrating in that it was nearly impossible to get a city going that generated any revenue before your city's funds went kaput and you were $100,000 in debt, the game's limit before you're kicked out of office), the game still is quite difficult if you leave it in its original state. Also, it took a while for me to grasp the concepts behind how the game works, specifically in areas of demand and "demand caps", which are limits placed on growth that must be overcome by placing certain buildings or making network connections, etc. Once you understand these, by either getting a strategy guide or simply reading forums (recommended, the strategy guide isn't as much help as I thought it would be).
Since I started playing the day I got the game (the day the original SC4 was released), I have grown to not only love the game, but to appreciate the community that has grown around the game, from my inital curiosity as to how the game's downloadable plugins worked to today, having founded a team of model makers *see last paragraph* at the Simtropolis community (www.simtropolis.com), probably the best place to go for information, help, tutorials, and custom content for SimCity 4. I have over 1 GB of plugins that not only add content to the game but change how it works and add entirely new aspects, such as new transit networks and intersections and bug fixes that Maxis never fixed in any of their patches. There are so many possibilities with what Maxis' programmers left the players, and many of these have been exploited (most of them are posted on Simtropolis' Exchange or the forums). The most notable of these is the BAT (Building Architect Tool), which this round is a plugin for gmax, a complex modeling utility which was recently discontinued. However, if you look around the internet, I'm sure you can find a download for it somewhere...or, some members of Simtropolis have created a "3dsBAT" that is a plugin for 3D Studio Max.
Anyways ... I cannot count the number of hours I have spent playing this game. To be honest, I'd guess easily beyond the 500 hour point. It is incredibly addictive and is a game you can always come back to, especially with a community like Simtropolis -- when you leave, you come back to find all sorts of new mods and tools and enhancements that will make you want to go back and play the game again!
If you're looking for a great game of this style (simulation) with a ridiculously great amount of replay value and customizability and "alterability" with third-party tools, not to mention a great community, this game is for you. I'll leave the rest of the details up to Gamespot's review, this is just a review from a more or less veteran player who has invested a lot of his time into the game.
*plug* On the topic of Simtropolis, I founded a model making and lot building team called "NDEX", an acronym for the members' original members. I am "Equinox" on Simtropolis' forums and Exchange (called the STEX). Visit the site and browse the forums, and check the STEX for some of NDEX's creations, in addition to searching for "Somy", an amazing Japanese modeler, some of whose creations I have brought to the Simtropolis Exchange -- with his permission, of course. If the site's like what it used to be, you have to register before you can browse the STEX, but trust me, it's well worth the effort. If you're trying to decide whether or not to buy this game, just look at some of the better lots on the Exchange and I bet you'll be inspired to go buy the game. :) You'll need a legal copy and a non-registered CD key to get patches from Maxis' official site in order to see custom BATs' nighttime lighting, so I recommend actually buying it, not downloading it. It's worth your money, especially if you liked previous SimCity titles.