Window function with a limit clause

Question

Given this dataset of teams

id | team_name | points
1  | A         | 10
2  | B         | 20
3  | C         | 30
4  | D         | 40
5  | E         | 50

i can use this query with a window function to get the average of the top 3 teams

with top_3 as(
  select * 
  from teams 
  order by points desc 
  limit 3
)

select *, avg(points) over() as top3_average
from top_3

Is is possible to use window functions on only the top 3 rows from the dataset?

I tried with, but that certainly doesn't work.

OVER(... limit 3)

Here is a working sqlfiddle

---

Just to be clear I know I can do this with a subquery like so. I'm more interested in learning if this is possible with a window function

select *, avg(points) over() as top3_average
from teams
where id in
  (select id 
   from teams 
   order by points desc 
   limit 3)

Answer 1

No, not really. You can't easily specify a FRAME that only includes the first three rows. You could get the average in the first row and put nulls everywhere else, but this just seems like a terrible idea:

select *, 
    avg(points) OVER (ORDER BY points desc ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING) 
    * CASE WHEN row_number() OVER (ORDER BY points desc) = 1 THEN 1 ELSE null END
FROM teams;
 id | team_name | points |      ?column?
----+-----------+--------+---------------------
  6 | F         |     60 | 50.0000000000000000
  5 | E         |     50 |
  4 | D         |     40 |
  3 | C         |     30 |
  2 | B         |     20 |
  1 | A         |     10 |
(6 rows)