Window functions in filter, sort & join

[aljazerzen]

from tbl1
filter (average bar) > 3

... produces:

WITH table_0 AS (
  SELECT
    tbl1.*
  FROM
    tbl1
)
SELECT
  table_0.*
FROM
  table_0
WHERE
  AVG(bar) OVER () > 3

... which uses window function in WHERE, which is not allowed. The window function should have been applied in first CTE and used only in SELECT in the second CTE.

I know average does not make sense in filter, but there could be lag or rank instead.