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

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;