0

Please login or register to vote for this query.

(click on this box to dismiss)

Find the highest-rep users whose combined reputation is more than X% of the total user reputation. Let's make Pareto proud.

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- Elite Users: Those who hold > X% of all Reputation
-- Find the highest-rep users whose combined reputation
-- is more than X% of the total user reputation.
-- Let's make Pareto proud.
declare @TotalReputation int
select @TotalReputation = sum(Reputation)
from Users

declare user_cursor cursor for
  select id, displayname, reputation from users order by reputation desc
open user_cursor


declare @id bigint
declare @DisplayName nvarchar(max)
declare @Reputation int

declare @RepSofar int
set @RepSoFar = 0

declare @NumberOfUsers int
set @NumberOfUsers = 0

fetch next from user_cursor into @ID, @DisplayName, @Reputation
while @@fetch_status = 0 and @RepSoFar < @TotalReputation * (##Percent## / 100.0)
begin
    set @RepSoFar = @RepSoFar + @Reputation
    set @NumberOfUsers = @NumberOfUsers + 1
  fetch next from user_cursor into @ID, @DisplayName, @Reputation
end

close user_cursor
deallocate user_cursor

--select @Reputation as CutoffReputation, @ID as CutoffID, @DisplayName as CutoffDisplayName, @TotalReputation as TotalReputation,  @NumberOfUsers as NumberOfUsers



-- This will list the actual users, but often exceeds the 2K row limit.
-- Note that you can only have one result set, so comment out the above select
-- before uncommenting this one

 select id, DisplayName, Reputation
 from users
 where Reputation >= @Reputation
 order by Reputation desc

view current version

edited may 23 12 anonymous

created jun 14 10 cwalker77