Read on to learn Window, array_agg & filter

Filter

Filter is to be used with aggregate functions or window functions. It allows us to filter out values

In this example we print average runner time and then filter on the runners weighing less than 90kg to produce the light_runners_time column which will result in bob (UK) being removed.

select country,
avg(time) as avg_time,
avg(time) filter (where weight < 90) as light_runners_time
from runners group by country

Array Agg

Array Agg is not a window function but it is interresting

Array Agg allows us to select several entries into one. Think of it as compressing the values into an Array object

select array_agg(time) from runners;

Window

Window allows us to name an SQL window function so it can be reused easily. It turns out to be very hard to write an example for it.