It's not that single-player is dead. It's that offline is dead (or dying). Which is, and I say this as a predominantly single-player game enthusiast, basically okay. Right now I'm playing two games pretty regularly, Need for Speed: Hot Pursuit and Joe Danger, which both have well integrated leaderboards. But they don't just pit you against millions of random people across the globe. They actually pit you against people on your friends lists.
So when I boot up NFS and get ready to tick off another event on that big map I instead skip over to the Autolog and see what my friends have been up to lately. I then spend the next hour and a half trying to beat their times and reclaim my top spot on the wall. So for a game where I would normally run straight through trying merely to complete every event and reach 100% completion, I'm now basically wasting time re-racing events competitively against my friends list. And you know what? I'm loving it. I think this is actually the best way to enhance replayability that I've seen in a long time. And it's not like leaderboards are anything new in games, far from it. But that connectedness is really addicting. I've yet to play one multi-player event. I will at some point but I'm still having fun with the single-player. Fun that indeed benefits from the connected, social features they've weaved into the game.
And yea I'm not a Facebook guy but from what I understand this is a pretty common thread among Facebook games as well. It's an interesting way to game.