Find the latest rating for each category for each customer

Question

The data in the rating table looks like this:

StudentID	Rating	ScoreType	Date
1	12	RAPID	2023-01-01
1	15	RAPID	2023-01-10
1	20	RAPID	2023-02-15
1	25	RAPID	2023-02-20
1	25	BLITZ	2023-03-01
1	33	BLITZ	2023-03-20
2	17	RAPID	2023-01-15
2	19	BLITZ	2023-02-06

What I am trying to build is like this:

StudentID	RAPID_Rating	RAPID_Games	BLITZ_Rating	BLITZ_Games	Total Games
1	25	4	33	2	6
2	17	1	19	1	2

The data should be one row for each student

Column definitions of the expected output:

RAPID_Rating : Latest rating value based on date where ScoreType = 'RAPID'

RAPID_Games: Total number of RAPID games played by each student

BLITZ_Rating: Latest rating value based on date where ScoreType = 'BLITZ'

BLITZ_Games: Total number of BLIRZ games played by each student

Total Games: Total games played by each student

The SQL Code that I have tried:

select
    coalesce(a.StudentID,b.StudentID) as StudentID,
    a.rating as RAPID_Rating,
    RAPID_Games,
    b.rating as BLITZ_Rating,
    BLITZ_Games,
    RAPID_Games + BLITZ_Games as Total_Games
    
from 
(select 
    StudentID, 
    ScoreType,
    rating,
    count(rating) over(partition by StudentID,ScoreType) as RAPID_Games,
    row_number() over(partition by StudentID,ScoreType order by date desc) as RNUM
from rating 
    where ScoreType ='RAPID'
) a join (
    select 
    StudentID, 
    ScoreType,
    rating,
    count(rating) over(partition by StudentID,ScoreType) as BLITZ_Games,
    row_number() over(partition by StudentID,ScoreType order by date desc) as RNUM
from rating 
    where ScoreType ='BLITZ'
) b on a.StudentID = b.StudentID 
where a.RNUM = 1 and b.RNUM = 1

I have to do this calculation for another 2 categories then that will add up another 2 sub queries. Is there anyway to optimize this SQL code?

Answer 1

We can use ROW_NUMBER along with conditional aggregation here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY StudentID, ScoreType ORDER BY Date DESC) rn
    FROM rating
)

SELECT
    StudentID,
    MAX(Rating) FILTER (WHERE ScoreType = 'RAPID' AND rn = 1) AS RAPID_Rating,
    COUNT(*) FILTER (WHERE ScoreType = 'RAPID') AS RAPID_Games,
    MAX(Rating) FILTER (WHERE ScoreType = 'BLITZ' AND rn = 1) AS BLITZ_Rating,
    COUNT(*) FILTER (WHERE ScoreType = 'BLITZ') AS BLITZ_Games,
    COUNT(*) AS "Total Games"
FROM cte
GROUP BY StudentID
ORDER BY StudentID;