Top N per group

Ranking inside a partition and then filtering on the rank is the standard recipe. The choice of ranking function decides what happens with ties.

ROW_NUMBER always gives exactly N rows but picks arbitrarily among ties. RANK keeps every tied row and leaves gaps. DENSE_RANK keeps ties without gaps. Say out loud which behaviour the question wants.

The same pattern works on aggregates: aggregate first in a CTE, then rank the summary rows.