This chapter described the advanced SQL concepts and features such as common table expressions and window functions. These features, for example, allow implementing a logic that would not be possible otherwise, that is, recursive queries.
The other techniques explained here, like the DISTINCT ON clause, grouping sets, the FILTER clause, or lateral subqueries, are not that irreplaceable. However, they can help in making a query smaller, easier, and faster.
SQL can be used to implement a very complicated logic. However, in difficult cases, the queries can become overcomplicated and very hard to maintain. Moreover, sometimes it is not possible to do some things in pure SQL. In these cases, one needs a procedural language to implement an algorithm. The next chapter will introduce one of them: PL/pgSQL.