Read on to learn Window, array_agg & 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 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 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.