No, your database will not detect the semantics of your query.
What they do do, and in excruciating depth, is do their darndest to execute your query as fast (or as efficient, depending on settings) as possible. Also, these kinds of operations are the bread and butter of the DB - everything from the hardware level up will be optimized fully to perform these operations as quickly as possible within the RDBMS.
In your particular case, there is no conceivable way for the RDBMS to do it differently. There is no generic syntax for specifying a "quickselect"-like operation in SQL. In other cases, sub-queries can and routinely are optimized together with their parents, but due to your construction with the analytic function (which you presumably picked like this for the purpose of this question, it is a little more complex than needed), I would not expect the RDBMS to figure it out.
Still. The big carrot for doing it in SQL instead of in your code is that this saves you from transferring an arbitrarily large amount of data over a possibly slow network - and not only the network, but the full stack ending behind the DB library of your application, in your actual code, including coding and recoding, validation etc.. This alone is, in my experience, almost always worth it for significant amounts of data (and for insignificant amounts it usually doesn't matter either way).
While the operation is $O(n\log n)$ in the DB, the constants may still make it faster or even much faster than $O(n)$ in your application, if it takes long to transfer all the data to it. Especially if you start scaling - imagine not only having to transfer lots of data to one application, but to many - you can easily hit a network bottleneck which is out of your control to remove; while bottlenecks on your RDBMS server are usually much more under your control (or you can throw indexes, materialized views etc. at the problem, restructure your SQL, optimize RAM usage and so on).
On your concrete questions:
The database is aware of specific patterns of queries and tries to use a data structure/algorithm to respond to the query (similar to how a human would handle it)?
No, it cannot usually sense complex patterns. But what they do have are the so-called "statistics" - i.e., histograms about the contents of their tables and indexes. Each time the RDBMS needs to process a SQL statement, it creates an "execution plan" which takes all these statistics, and of course also all available algorithms, data structures and such into account.
In the distant past, RDBMS would instead use rule-based plans which were pretty stupid. But with the statistics-based approach, if all goes well (and for regular statements on a well-tuned system, that's mostly a given in a modern RDBMS) then at the end of the day it comes up with a solution equal or better to what a human can do, simply because it usually has more information.
Exceptions to that rule are where good DBAs or DB-centric devs shine and make their money.
The database provides options for humans to nudge the engine towards specific types of algorithms/data-structures?
Sure. At least the RDBMSses I know (foremost Oracle) give the human a plethora of options; both through creating specialized indexes and other structures, and through setting different options on all the data structurs; through tuning system settings (relative sizes of RAM buffers for different things; different weights on preferring certain algorithms or solutions); and finally through so-called "hints" in individual SQL queries with a non-SQL meta-language.
What prevents us from being able to describe queries and have a database engine figure out a near-optimal approach to solve the query?
Nothing is preventing us, and that is the way it should work. That's what SQL is all about - while one may naively assume that SQL is an instruction of how to work, it is most definitely not. A RDBMS is free to interpret it and plan the actuall work in whichever way it sees fit, and the art is to formulate the SQL in a way which is letting the execution planner do its job (i.e., to avoid something like in your example which pretty much shoe-horns it into a specific, non-optimal solution), but then to get out of its way.