Finding most high-rep users among multiple SE sites

1

Please login or register to vote for this query.

(click on this box to dismiss)

Parametrisation is comma-separated list of database names. The DB names are coming as ou can see it from SELECT * FROM sysdatabases;

Sitecore

Q&A for developers and end users of the Sitecore CMS and multichannel marketing software

DECLARE @xml xml, @str VARCHAR(MAX);
SET @str = ##Databases:string?StackOverflow,StackExchange.Math,StackExchange.Physics,StackExchange.Unix,ServerFault##;
SET @xml = cast(('<X>'+replace(@str, ',', '</X><X>')+'</X>') as xml);

DECLARE @databases TABLE (
  name VARCHAR(MAX)
);

DECLARE @q1_select VARCHAR(MAX) = 'SELECT ';
DECLARE @q1_from VARCHAR(MAX) = 'FROM ';
DECLARE @q1_where VARCHAR(MAX) = 'WHERE ';

DECLARE @query2 VARCHAR(MAX) = '';

DECLARE @dbname VARCHAR(MAX);
DECLARE @first VARCHAR(MAX) = '';
DECLARE @second INT = 0;

DECLARE dbcursor CURSOR FOR
SELECT C.value('.', 'VARCHAR(MAX)') AS [name]
FROM @xml.nodes('X') AS X(C);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @first = ''
  BEGIN
    SET @first = @dbname
    SET @q1_select = @q1_select + '[' + @dbname + '].AccountId'
  END
  ELSE
  BEGIN
    SET @q1_from = @q1_from + ', '
    IF @second = 0
      SET @second = 1
    ELSE
      SET @q1_where = @q1_where + '
  AND ';
    SET @q1_where = @q1_where + '[' + @first + '].AccountId = ['
    + @dbname + '].AccountId';
    SET @query2 = @query2 + 'UNION';
  END

  SET @q1_from = @q1_from + '[' + @dbname + '].dbo.Users [' + @dbname + ']';
  
  SET @query2 = @query2 + '
SELECT
  #Users.UserId AS UserId,
  [' + @dbname + '].dbo.Users.DisplayName AS UserName,
  ''' + @dbname + ''' AS DbName,
  [' + @dbname + '].dbo.Users.Reputation AS Rep
FROM [' + @dbname + '].dbo.Users, #Users
WHERE Users.AccountId = #Users.UserId
';
  FETCH NEXT FROM dbcursor INTO @dbname;
END;

DECLARE @query1 VARCHAR(MAX) = @q1_select + '
' + @q1_from + '
' + @q1_where;

CREATE TABLE #Users (
  UserId INT
);

PRINT @query1;
PRINT @query2;

INSERT INTO #Users EXEC (@query1);

CREATE TABLE #Accounts (
  UserId INT,
  UserName VARCHAR(MAX),
  DbName VARCHAR(MAX),
  Rep INT
);

INSERT INTO #Accounts EXEC (@query2);

WITH Raw AS (
  SELECT UserId, MIN(Rep) AS MinRep
  FROM #Accounts
  GROUP BY UserId
)
SELECT TOP 100
  DENSE_RANK() OVER (ORDER BY Raw.MinRep DESC) AS Rank,
  *
FROM #Accounts, Raw
WHERE #Accounts.UserId = Raw.UserId
ORDER BY Raw.MinRep DESC, Raw.UserId, #Accounts.DbName

Enter Parameters

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