What will be the query for this?

    JOIN public.match m ON (s.stadium_id = m.stadium_id)
group

AS (

)

SELECT round_number ,stadium_name ,spectators FROM ( SELECT round_number ,stadium_name ,spectators ,RANK() OVER ( PARTITION BY round_number ORDER BY spectators DESC ) AS rank1 FROM t1 ) AS s1 WHERE rank1 = 1

<br>
Any smaller query than this?

Answer

I think you can just use window functions:

select ms.*
from (select m.round_number, s.stadium_name, m.no_spectators,
             row_number() over (partition by m.round_number order by m.no_spectators desc) as seqnum
      from public.stadium s join
           public.match m 
           on s.stadium_id = m.stadium_id
     ) ms
where seqnum = 1
order by m.round_number;

I don’t see why aggregation would be needed for the inner query.