I've usually always regretted those "awesome" queries.
I've known some very smart developers who've gotten lost in SQL queries, while show them the equivalent Ruby/Python/Javascript/C code that parses through the results and they can understand it less than a minute.
For example, I once worked on a Foursquare clone originally written by a hardcore PostgreSQL nut. This system had a query that, if memory serves, returned a list of places of a certain type within a geographic area along with user activity on those places (votes, comments, etc). This was around a 75 line SQL query that actually wasn't that fast (response times from the DB were roughly 1 second even with every join indexed). We rewrote that query into 4 smaller queries (place ID's within that area, place ID's within that category, hydrating those places from the filtered ID's and then getting the user info), and that cut our DB response by about 70% in addition to making the system easier to work with. This required roughly 10 lines of Java code and a variable - a list of ID's that we got first and passed into each other query. It also freed us up to do other things - for instance, if performance were still a problem, queries 2-4 could have been done asynchronously behind a latch. By lifting the "glue" out of SQL and into a better language, it freed us to do new things, and it freed the database from having to juggle unnecessary complexity while planning and executing its queries.
But the query is assembled piece by piece, in separate functions, each subquery responsible for its own contribution to the final query string, with well-defined inputs and outputs. The entire file that generates the query reads quite logically.
And there's simply no alternative -- many pieces of processing involves 100,000+ rows, so round-trips between db and app would be prohibitively slow. The whole thing uses data from around 10 different tables, it's extremely relational.
But because it's structured well and written correctly, the whole thing executes in a small fraction of a second. (Trying to do it in a "NoSQL" style would probably take ten minutes of back-and-forth network communications.)
I've known a lot of programmers who would shy away from such a thing -- but that's because a lot of programmers don't bother to actually understand SQL the way they understand Ruby or JavaScript or PHP. It can do amazing feats of data processing, which is the whole point of a relational database. My advice is, dig deep into SQL. It can work wonders, but it's true that its "best practices" can be difficult to learn, and there's a lot of bad advice out there.
One thing SQL really helps is it force you to think "data first". Instead of thinking algorithms, step by step what you want to do, it makes you think along the line: what data I got and what output I want to get out of it, not unlike functional programming, but with more focus on data sets.
http://www.postgresql.org/docs/9.2/static/queries-with.html
I first saw this demonstrated in Peter van Hardenberg's excellent Waza 2013 presentation, "Postgres: The Bits You Haven't Found":
SQL is much more powerful than many realise, but there's a great many developers who aren't as good at it as they think.
One of the big things is that there are no 'for' loops in good SQL code - i.e. it is written using set-based logic and not procedural logic. That can (sometimes) really cut down the amount of reading involved, but takes a while to get used to.
Also depends how well you understand the schema in question (but that compares equally to understanding the code framework/namespace hierarchy).