Most hardcore dual-site synergists

1

Please login or register to vote for this query.

(click on this box to dismiss)

Shows for all site-pairs the users whose minimal rep among them is the maximal.

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

CREATE TABLE #Accounts (
  DbId INT,
  UserId INT,
  Rep INT
);

DECLARE @query VARCHAR(MAX);

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

DECLARE @DbName VARCHAR(MAX);
DECLARE @DbId INT;

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @DbId, @DbName;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    SELECT
      ''' + CAST(@DbId AS VARCHAR(MAX)) + ''' AS DbId,
      Users.AccountId AS UserId,
      Users.Reputation AS Rep
    FROM [' + @DbName + '].dbo.Users
    WHERE
      Reputation >= 202
  ';

  INSERT INTO #Accounts EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @DbId, @DbName;
END

CLOSE dbcursor;

WITH Raw AS (
  SELECT
    A1.DbId AS Db1Id,
    A2.DbId AS Db2Id,
    MAX(CASE WHEN A1.Rep < A2.Rep THEN A1.Rep ELSE A2.Rep END) AS MaxMinRep
  FROM #Accounts A1, #Accounts A2
  WHERE
    A1.DbId < A2.DbId
    AND
    A1.UserId = A2.UserId
  GROUP BY A1.DbId, A2.DbId
)
SELECT
  sysdb1.name AS Db1Name,
  sysdb2.name AS Db2Name,
  Raw.MaxMinRep AS MaxMinRep,
  'https://data.stackexchange.com/stackoverflow/query/1160628?Databases='
    + sysdb1.name + ',' + sysdb2.name
    + '|Most hardcore guys' AS 'Most hardcore guys'
FROM Raw, sysdatabases sysdb1, sysdatabases sysdb2
WHERE
  Raw.Db1Id = sysdb1.DbId
  AND
  Raw.Db2Id = sysdb2.DbId
ORDER BY MaxMinRep DESC, Db1Name, Db2Name;

Enter Parameters

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