Find the latest rating for each category for each customer

The data in the rating table looks like this:

What I am trying to build is like this:

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?

We can use ROW_NUMBER along with conditional aggregation here:

<!-- language: sql -->

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;