Top square-posts-index

0

Please login or register to vote for this query.

(click on this box to dismiss)

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.

Stack Overflow

Q&A for professional and enthusiast programmers

-- Rank posts for each user highest to lowest.
; WITH PostIndex (UserId, Score, Rank)
AS (
  SELECT OwnerUserId, Score, 
    RANK() OVER (PARTITION BY OwnerUserId ORDER BY Score DESC, Id)
  FROM Posts
  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
  PostIndex.UserId, 
  MIN(PostIndex.Score) AS MinScore,
  MAX(Users.Reputation) AS Reputation,
  MAX(PostIndex.Rank) AS SquarePostIndex
FROM PostIndex
LEFT JOIN Users ON Users.id = PostIndex.UserId
WHERE PostIndex.Score >= PostIndex.Rank
GROUP BY PostIndex.UserId
ORDER BY MAX(PostIndex.Rank) DESC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results