StackOverflow Per-Day and Overall Rank and Percentile

2

Please login or register to vote for this query.

(click on this box to dismiss)

Gives a user's reputation rate (per day) and ranking of rate and overall reputation within the set of users with Reputation > 100.

Emacs

Q&A for those using, extending or developing Emacs

-- StackOverflow Per-Day and Overall Rank and Percentile
-- Gives a user's reputation rate (per day) and ranking of rate and overall reputation within the set of users with Reputation > 100.

SET NOCOUNT ON;

DECLARE @UserID int = ##UserId##;
DECLARE @endDate date;
SELECT @endDate = max(CreationDate) from Posts;

SET NOCOUNT OFF;

DECLARE @Counts int;
SELECT @Counts = COUNT(*) FROM Users WHERE Reputation > 100;



WITH RepPerDays AS (
SELECT *, RepRate = CAST(Reputation AS decimal(10, 1))/ISNULL(NULLIF(CONVERT(int, @endDate - CreationDate) + 1, 0), 1)
FROM Users
)
,Rankings AS (
SELECT Id, Reputation, RepRate,
RankingRate = ROW_NUMBER() OVER(ORDER BY RepRate DESC),
RankingRep = ROW_NUMBER() OVER(ORDER BY Reputation DESC)
FROM RepPerDays
)


SELECT Id,
Reputation, RankingRep, 100 - (RankingRep * 100.0) / @Counts AS PercentileRep,
RepRate, RankingRate, 100 - (RankingRate * 100.0) / @Counts AS PercentileRate
FROM Rankings
WHERE Id = @UserID

Enter Parameters

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