Cross-network mercenaries

1

Please login or register to vote for this query.

(click on this box to dismiss)

People by MIN(OfferedRep, WonRep), Network-wide

Stack Overflow

Q&A for programmers

DECLARE @bounty TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  AccountId INT NOT NULL,
  OfferedRep INT NOT NULL,
  WonRep INT NOT NULL
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE
  name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange')
  AND
  name NOT LIKE '%Temp%';

DECLARE @dbname VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + @dbname + ''' AS DbName,
      MIN(Users.DisplayName) AS UserName,
      Users.AccountId,
      SUM(CASE WHEN Votes.VoteTypeId = 8 THEN Votes.BountyAmount ELSE 0 END)
        AS OfferedRep,
      SUM(CASE WHEN Votes.VoteTypeId = 9 THEN Votes.BountyAmount ELSE 0 END)
        AS WonRep
    FROM
      [' + @dbName + '].dbo.Votes,
      [' + @dbName + '].dbo.Posts,
      [' + @dbName + '].dbo.Users
    WHERE
      Votes.VoteTypeId IN (8, 9)
      AND
      Votes.BountyAmount > 0
      AND
      Votes.PostId = Posts.Id
      AND
      Posts.OwnerUserId = Users.Id
    GROUP BY Users.AccountId
  ';
  INSERT INTO @bounty EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH Raw AS (
  SELECT
    MIN(UserName) AS UserName,
    AccountId,
    SUM(OfferedRep) AS OfferedRep,
    SUM(WonRep) AS WonRep
  FROM @bounty
  GROUP BY AccountId
),
Raw2 AS (
  SELECT *,
    CASE
      WHEN OfferedRep > WonRep THEN WonRep
      ELSE OfferedRep
    END AS Rep
  FROM Raw
)
SELECT
  DENSE_RANK() OVER (ORDER BY Rep DESC) AS Rank,
  'https://stackexchange.com/users/' + CAST(AccountId AS VARCHAR(MAX))
    + '?tab=accounts|' + UserName AS 'Network Profile',
  OfferedRep,
  WonRep,
  AccountId,
  'https://data.stackexchange.com/stackoverflow/query/678540?AccountId='
    + CAST(AccountId AS VARCHAR(30))
    + '|' + UserName AS 'All SE Accounts'
FROM Raw2
ORDER BY Rep DESC, AccountId;

Enter Parameters

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