Users offering the most reputation through bounties (networkwide)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

create table #results (site nvarchar(250)
                       , accountid int
                       , reputation int
                       , displayname nvarchar(40)
                       , numberofbounties int
                       , reputationoffered int);

declare @sql nvarchar(max) = ''

select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
N'
select ''' + name + '''
     , u.accountid
     , u.reputation
     , u.displayname
     , count(*)
     , sum(v.bountyamount)
from ' + quotename(name) + '.dbo.votes v
inner join ' + quotename(name) + '.dbo.users u on v.userid = u.id
where v.votetypeid = 8 -- BountyStart
  and u.id != -1 -- Exclude Community user
group by u.accountid, u.reputation, u.displayname '
from sys.databases
where database_id > 5
  and (name not like '%.Meta' or name = 'StackExchange.Meta')

exec (@sql)

select rank() over (order by sum(reputationoffered) desc) as 'Rank',
  'https://stackexchange.com/users/' + CAST(accountid AS nvarchar)
  + '|' + MAX(displayname) AS 'User',
  sum(reputationoffered) AS 'Reputation offered',
--round(100.0 * SUM(reputationoffered) / (sum(reputation) + SUM(reputationoffered)), 2) AS 'Percentage',
  sum(numberofbounties) AS 'Total bounties',
  count(*) AS 'Total sites',
  max(reputationoffered) AS 'Max reputation offered on 1 site'
  from #results
  group by accountid
  order by sum(reputationoffered) desc

drop table #results

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results