What current implementations of rdbms's gain you is the ability to write completely ad-hoc queries and get reasonable performance most of the time. This is an implementation advantage, not a theoretical advantage.
Also, in the authors example the items are properties of the order. How exactly would you index on those?
I'll stop you there - filters can use database indexes. Exactly the same way SQL databases do it. Database driver chooses how it fetches those results. Getting the records based on some criteria and indexed attributes does not iterate through every record.
In your "Most popular" example, you're still iterating records locally (and sorting locally) to calculate the results rather than having it done on the server. You've also presupposed that you have normalized data -- which in authors example, you do not.
My point about having the RDBMS optimize your query still stands. Your code and your SQL statements are not the same -- the SQL is a description of the result but your code is the actual process. And you've over-simplified the process; in reality it would be much more complicated.
As mentioned before - I could put the "most popular" script on the server and request only the result if you wanted. TT can do that for sure and I remember reading about another solution that may (redis I think? I may be wrong here).
About the normalised or not data - you have to design your data storage to your solution and needs. If you made something hard to query, maybe it's time to redesign it, or add another denormalised storage, or keep aggregate copies, or use some other solutions... Same applies to rdbmses really.
You know how to run your queries on your DB efficiently (or you should start learning now). Sure - exploratory searching for patterns, custom reports, etc. - that's where SQL is great. If you need it, either you should have additional "archives" in some better data store (sql-capable column-based store?), or simply use rdbms for everything. Over-simplification is as bad as over-generalising ;) just use what you need, instead of what is popular.
I understand your point. But once you have normalized data, don't you lose most/all the advantages of a NoSQL solution? RDBMS's are designed to handle the searching, joining, and filtering of normalized data -- NoSQL solutions are not.
My original comment about "lack of imagination" applies -- If you're designed your data to handle as many scenarios as possible (which obviously the author of the article did not) then you will end up with something that is fully normalized.
If you need performance, whether it be in an RDBMS or not, you'll need to sacrifice some flexibility. But it seems like with NoSQL you're prematurely optimizing -- picking a solution with a very specific use-case when you're most likely not going to need any of the supposed advantages.
I'm not suggesting that NoSQL doesn't have it's use, but most of these articles advocate using it by default and in situations where it's clearly not appropriate. The author of this article, using it for e-commerce orders, seems like a very inappropriate use to me.
As for API, you can hide the SQL pretty well behind an abstraction as well. But there is an advantage to using SQL itself -- the database engine analyzes your query and produces the most optimal way to get the answer you want. You're choosing to write poorly optimized code instead.