[tacosbane]

I think every dialect has an idiomatic way to do what you're asking. e.g., in Snowflake it's `select * from sales_department_details qualify no_products_sold = max(no_products_sold) over (partition by sales_person_id)`, PSQL `select distinct on (sales_person_id) * from sales_department_details order by sales_person_id, no_products_sold desc`, ...

[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.