I don't think anyone suggests that it's that simple. This entire space is young and evolving quickly. Identity and data sync is a good place to start, though.
For your example of a leaderboard, Parse has Cloud Code. It's designed to do exactly what you describe -- define a function that sits on Parse's server, handles client queries against your data, and returns the relevant data. Their example is for computing the average rating for a movie from your users' reviews: https://parse.com/docs/cloud_code_guide#functions
Amazon, Google, and Microsoft aren't there yet, but I can only imagine it's on their roadmap. And Parse's implementation only works for fairly easy stuff. But I think things will look very different in 5 years.
Parse's solution is a great start as you say, but I can see myself running into the limits of that approach fairly quickly.
And, as you say, if these solutions mature, we might have some very powerful stuff in this space in a few years... maybe almost as powerful as just having your own server-side code running on some backend servers ;) But at that point, we're right back to where we are with traditional/custom backend systems being run on cloud server providers (which is not a bad thing!).
It just kind of feels like we're trying to re:Invent the wheel. You can offer these kinds of "a la carte" services all day long, and they're nice (on the surface) as long as they play nicely with each other in an open ecosystem - e.g., "let's grab this identity provider, that storage solution, this analytics engine, etc." It might look similar to Heroku's "add-ons", but with components that perform more specific functions. I just don't see that openness coming from Amazon, Google, etc. as they will all want to lock people into their services (which is understandable from a business standpoint). And unfortunately, anyone who buys into partial (and proprietary) solutions in the meantime are - in my opinion - just shooting themselves in the foot by limiting the use of their data.
Even in a perfect world with a vast marketplace of plug-and-play, interoperable cloud components, I would ask: is the "soul" of your app's code really captured in a conglomeration of components like this? If you have a need for a backend with custom logic of any kind, I think you're probably doing something unique (hopefully anyway). If you're going to need some real, unique business logic that can't be offered as an abstract cloud component, then I strongly suspect you're going to want more control over all of the backend. Even Parse's approach ends up looking more and more like a traditional custom backend system as you add more power to the "Cloud Code" approach.
As a programmer, you can often find libraries and drop-in components that accomplish 80% of what you need, so you write some custom code to "glue" those components together. That custom code usually contains business logic that really couldn't be abstracted or offered as a drop-in component. Where does that custom code live in the "perfect" ecosystem of plug-and-play cloud server components? How do you control the thing which makes your app/backend business logic unique? I don't think it's going to be in the client/mobile app as some people tend to think.
Maybe there's a good solution that combines these kinds of components with custom (and flexible) server-side code - I just haven't seen what it looks like yet. If I tried to use this stuff today, I'd be kicking myself tomorrow as I figure out that it's a pain to juggle data between components that I don't control and components that I do control.
I was commenting to a friend that the limited functionality in these types of services have very steep edges. It's great when you're in the middle, but trying to do something a little bit different can lead to long, poor, difficult code as you try to work around the constraint.
I think we'll start seeing more "opinionated" platforms, similar to what Rails and others have done for web development. Parsing query strings, managing database connections, and wiring your own user reg code are all distant memories for most web developers most of the time today.
We might see something similar here, where groups of common functions or services are stacked together to form a unit that works well together and covers some set of use cases very well. LAMP comes to mind as a comparison, and you can definitely use SQL Server instead of MySQL, but it probably won't work as well when it comes down to drivers, libraries, recruiting, etc.
These stacks will differ as well to serve a broad range of customers. I cringe a bit when I see Amazon EC2 comparisons to Hetzner or Digital Ocean because they are serving quite a different set of customers with different needs. To think that all hosts should end up standardizing on the same units would leave many customers unserved. It's easy for innovation to be called vendor lock-in, but it's hard to compete or innovate on heavily commoditized products/services.
For your example of a leaderboard, Parse has Cloud Code. It's designed to do exactly what you describe -- define a function that sits on Parse's server, handles client queries against your data, and returns the relevant data. Their example is for computing the average rating for a movie from your users' reviews: https://parse.com/docs/cloud_code_guide#functions
Amazon, Google, and Microsoft aren't there yet, but I can only imagine it's on their roadmap. And Parse's implementation only works for fairly easy stuff. But I think things will look very different in 5 years.