Time to put the pieces together. The strongest Codémon of each trainer is a classic question, and you already know one answer — a correlated subquery:
↗️
Loading editor...
↶
↷
It works, but it is rigid. The window version numbers the Codémons inside each team, strongest first, then keeps position 1. A window cannot sit in WHERE, so the numbering goes in a CTE — the CTE, then filter pattern again:
↗️
Loading editor...
↶
↷
Same three leaders. The advantage of the window: change rn = 1 into rn <= 2 and you get the top two per team — the subquery would need a full rewrite.
The pattern
1) CTE: SELECT …, ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) AS rn. 2) Main query: WHERE rn = 1 (or rn <= N). It answers "latest per customer", "first order of each day", "top 3 per category" — any best-of-each-group question.