0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Graphic Design professionals, students, and enthusiasts

DECLARE @output TABLE (
  UserName VARCHAR(max),
  AccountId INT,
  Upvotes INT,
  Downvotes INT,
  TotalVotes INT
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')

DECLARE @dbname VARCHAR(max);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
     SELECT
        DisplayName AS UserName,
        AccountId,
        Upvotes,
        Downvotes, 
        UpVotes + DownVotes AS TotalVotes
      FROM [' + @dbname + '].dbo.Users
      WHERE UpVotes + DownVotes >= 10
    ';
  INSERT INTO @output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @dbname;
END

CLOSE dbcursor;

WITH CombinedVotes AS (
  SELECT
    MIN(UserName) AS UserName,
    AccountId,
    SUM(TotalVotes) AS TotalVotes,
    SUM(Upvotes) AS Upvotes,
    SUM(Downvotes) AS Downvotes
  FROM @output
  GROUP BY AccountId
)
SELECT TOP 5000
  DENSE_RANK() OVER (ORDER BY TotalVotes DESC) AS Rank,
  'http://stackexchange.com/users/'
    + CAST(AccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'SE Account',
  AccountId,
  Upvotes,
  Downvotes,
  TotalVotes
FROM CombinedVotes
ORDER BY TotalVotes DESC, AccountId;

fork query

edited apr 7 19 jon.doe14699

created aug 15 17 peterh