Q&A about the site for linguists, teachers and students of the Ukrainian language
SELECT Id, P.PostTypeId, Score, 'Max', 'Count', RANK() over (PARTITION BY P.PostTypeId ORDER BY Score DESC) as 'Rank'
FROM Posts as P LEFT JOIN
(SELECT PostTypeId, MAX(Score) as 'Max', COUNT(*) as 'Count'
GROUP BY PostTypeId
) as M ON (P.PostTypeId = M.PostTypeId)
ORDER BY P.PostTypeId, 'Rank'