Search code examples
sqlmaxgreatest-n-per-group

SQL query to get top performer statistics per year (eg 3 pointers)

I have a table with players per year and some stats. For example (player_key, player_name, year, 3point_trials, 3point_score)

I want to have as an ouput the top performer per year (3point_score/3point_trials). If two of them have the same % then the breakeven is the the 3point_trials, whoever has the most trials.

Output would be per year

Name, Year, %score, trials
Player1 2000 55 1200
Player2 2001 61 1001
Player1 2002 54 978
Player6 2003 63 1034
Player5 2004 59 1132
.....

what is the SQL query for this output?

Solution

  • I would recommend window functions:

    select p.*
from (
    select p.*, 100.0 * 3point_score / 3point_trials score_percent,
        rank() over(partition by year order by 100.0 * 3point_score / 3point_trials desc, 3point_trials desc) rn
    from players p
) p
where rn = 1
order by year, rn