Using basic statement window functions

As we saw in the previous chapter, aggregation functions behave in the following way:

Figure 6.1: Standard group by aggregation

The data is first sorted and then aggregated; the data is then flattened through aggregation. This is what happens when we execute the following statement, after connecting as the forum user to forumdb database:

forumdb=> select category,count(*) from posts group by category order by category;
 category | count
----------+-------
        1 |     2
        3 |     1
(2 rows)

Alternatively, we can decide to use window functions by executing the following statement:

forumdb=> select category, count(*) over (partition by category) from posts order by category;
 category | count
----------+-------
        1 |     2
        1 |     2
        3 |     1
(3 rows)

Window functions create aggregates without flattening the data into a single row. However, they replicate it for all the rows to...