[hantusk]

Postgres can do the"function over (partition by col_a order by col_b) as well.

Agree, SQL can already do what the OP proposes in almost the exact same syntax.

and together with CTEs, intermediate temp tables or views, you can totally avoid the spaghetti and break down the queries in manageable chunks.

[tacosbane]

postgres has window functions but it does not have the `qualify` clause.

[ttfkam]

True, but you can simulate it by running the query with the window function in a CTE and then make a separate SELECT with the appropriate WHERE clause. More verbose, but the same effect and result. Using NOT MATERIALIZED on the CTE can also potentially let the planner do better query optimization on it.