Learning PostgreSQL 11 - Third Edition

Window functions with grouping and aggregation

As window functions are evaluated after grouping, it's possible to use aggregating functions inside window functions, but not the other way around.

The code shown here is correct:

sum(count(*)) OVER()

The following approach will also work:

sum(a) OVER(ORDER BY count(*))

However, sum(count(*) OVER()) is wrong.

For example, to calculate the rank of the seller accounts by the number of advertisements they make, the following query can be used:

car_portal=> SELECT seller_account_id, dense_rank() OVER(ORDER BY count(*) DESC) FROM car_portal_app.advertisement GROUP BY seller_account_id; seller_account_id | dense_rank-------------------+------------                26 |          1               128 |          2                28 |          2               126 |          2...