1

Please login or register to vote for this query.

(click on this box to dismiss)

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

Q&A for users and developers of hardware and software for Raspberry Pi

DECLARE @bounty TABLE (
  DbName VARCHAR(MAX) NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  AccountId INT NOT NULL,
  Rep 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(Votes.BountyAmount) AS Rep
    FROM
      [' + @dbName + '].dbo.Votes,
      [' + @dbName + '].dbo.Posts,
      [' + @dbName + '].dbo.Users
    WHERE
      Votes.VoteTypeId = 9
      AND
      Votes.BountyAmount > 0
      AND
      Votes.PostId = Posts.Id
      AND
      Posts.OwnerUserId = Users.Id
      AND
      Posts.PostTypeId = 2
    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(Rep) AS Rep
  FROM @bounty
  GROUP BY AccountId
)
SELECT
  DENSE_RANK() OVER (ORDER BY Rep DESC) AS Rank,
  'https://stackexchange.com/users/' + CAST(AccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'Network Profile',
  Rep,
  AccountId,
  'https://data.stackexchange.com/stackoverflow/query/678540?AccountId='
    + CAST(AccountId AS VARCHAR(30))
    + '|' + UserName AS 'All SE Accounts'
FROM Raw
ORDER BY Rep DESC, AccountId;

view current version

edited sep 16 17

created jul 19 17 peterh