Usage of aggregate function with when self-joining a table
Assuming a table like:

CREATE TABLE player
    ( player_id int not null primary key
    , weight int not null
    , nationality char(2) not null -- just any additional attribute
    );

insert into player (player_id, weight, nationality)
values (1,70,'SE'), (2,75,'RU'), (3,60,'US');    


The following query appears to work in sqllite (as far as I can tell this is valid SQL99 since p1.weight, p1.nationality is functionally dependent of p1.player_id. Most vendors still implement the more restrictive SQL92 rule for group by). Also note that I prefer ansi join over "," joins, I find them easier to read.

select p1.player_id, p1.weight, p1.nationality
from player p1
cross join player p2
group by p1.player_id
having p1.weight &gt; avg(p2.weight);


having is just syntactic sugar for the chattier:

select player_id, weight, nationality
from (
    select p1.*, avg(p2.weight) as avg_weight
    from player p1
    cross join player p2
    group by p1.player_id
) as t 
where weight &gt; avg_weight;


Another variant is to join against a derived table, pretty much the same query as your original:

select p1.*
from player p1
cross join (
  select avg(weight) as avg_weight
  from player
) as p2 
where p1.weight &gt; p2.avg_weight;


On the other hand you can use window functions to achieve the same thing:

select player_id, weight, nationality
from (
    select p.*
         , avg(weight) OVER () as avg_weight 
    from player p
)
where weight &gt; avg_weight;


You can find an example at: db-fiddle