Most hardcore guys among site trios

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

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

Enter Parameters

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