Introduction
PARTITION BY
7. Range of OVER(PARTITION BY)
Revision

Instruction

That's right! Remember: window functions only work for those rows which are indeed returned by the query. Take a look at this query:

SELECT
  id,
  model,
  max_speed,
  COUNT(id) OVER (PARTITION BY max_speed)
FROM train
WHERE production_year != 2012;

We cut out the trains with production_year = 2012 and the query would not show them – that's pretty obvious. But the window function would not even count them – we could find out that there are only 2 trains with max_speed = 240, even though there is a third one which was produced in 2012. Note that a GROUP BY clause with a WHERE clause will behave in the same way – GROUP BY will only take into account rows which match the condition(s).

Exercise

Show id, model,first_class_places, second_class_places, and the number of trains of each model with more than 30 first class places and more than 180 second class places.

Console

Code editor

Result

TableConsole