new query for user total rank

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_users ( site nvarchar(250)
                            , userid int
                            , reputation int
                            , accountid int
                            , displayname nvarchar(250));

-- create index #idx_userid on #all_users(userid)
create index #idx_aid on #all_users(accountid, reputation desc)

select @sql = string_agg(concat(N'
select ''', name, N'''
     , id
     , reputation
     , accountid
     , displayname
     from ', convert(nvarchar(max),quotename(name)), N'.dbo.users
     where id > 0 -- no community
     and reputation > 1
'),
N'
union all
')
from sys.databases 
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
and ([name] not like '%.Meta' or [Name] = 'StackExchange.Meta') 

print @sql
select @sql = Concat(N'insert into #all_users ', @sql)
exec (@sql)

;with totrep as
(
select accountid
     , sum(reputation) [reputation]
from #all_users
group by accountid
)
, main_rank as
(
select rank() over(order by reputation desc) as [ranking]
     , accountid
     , reputation
from totrep
)
, all_users_rownum as -- might be moved into a temp table instead with an index
(
select accountid -- index
     , userid
     , site
     , reputation
     , displayname
     , row_number() over(partition by accountid order by reputation desc) as rownum
from #all_users
)
, without_main_rank as 
(
select rank() over(order by sum(reputation) desc) as [ranking]
     , accountid
     , sum(reputation) [reputation]
from all_users_rownum
where rownum > 1 --not main
group by accountid
)
, main_rank_rownum as
(
select row_number() over(order by ranking) [rownum]
     , *
from main_rank
)
, without_main_rank_rownum as 
(
select row_number() over(order by ranking) [rownum]
     , *
from without_main_rank
)


-- quick hack to get user at the top
select 0 rownum
     , m.ranking
     , mu.site
     , mu.displayname
     , m.[reputation]
     , s.ranking
     , su.site
     , su.displayname
     , s.[reputation]
from main_rank_rownum m
inner join without_main_rank_rownum s on s.rownum = m.rownum
inner join all_users_rownum mu on mu.accountid = m.accountid and mu.rownum = 1
inner join all_users_rownum su on su.accountid = s.accountid and su.rownum = 2
where 
-- yes, I hate my self 
   m.accountid = 1327177  
or s.accountid = 1327177
union all
select m.rownum
     , m.ranking
     , mu.site
     , mu.displayname  -- add new site url logic here
     , m.[reputation]
     , s.ranking
     , su.site
     , su.displayname  -- add new site url logic here
     , s.[reputation]
from main_rank_rownum m
inner join without_main_rank_rownum s on s.rownum = m.rownum
inner join all_users_rownum mu on mu.accountid = m.accountid and mu.rownum = 1
inner join all_users_rownum su on su.accountid = s.accountid and su.rownum = 2
order by rownum 

/*
select *
from
mainrank
where accountid = 1327177
-- order by sum(reputation)
*/

Enter Parameters

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