Top square post index users and their reputation.
Square Post Index is the highest post score for which they have at least that many posts of that score or higher.
E.g. square post index 21 indicates that the user has at least 21 posts with score 21 or higher, and that is the highest score for which that can be said. They don't have 22 posts with score 22 or higher.
Q&A for professional and enthusiast programmers
-- Rank posts for each user highest to lowest.
; WITH PostIndex (UserId, Score, Rank)
SELECT OwnerUserId, Score,
RANK() OVER (PARTITION BY OwnerUserId ORDER BY Score DESC, Id)
WHERE OwnerUserId IS NOT NULL
-- Rank is a count of the numer of posts at that score or above.
-- Get the Min score that matches the Max rank where score >= rank.
SELECT TOP 100
MIN(PostIndex.Score) AS MinScore,
MAX(Users.Reputation) AS Reputation,
MAX(PostIndex.Rank) AS SquarePostIndex
LEFT JOIN Users ON Users.id = PostIndex.UserId
WHERE PostIndex.Score >= PostIndex.Rank
GROUP BY PostIndex.UserId
ORDER BY MAX(PostIndex.Rank) DESC