List of all moderator election nominations, network-wide

1

Please login or register to vote for this query.

(click on this box to dismiss)

Poker Meta

Q&A about the site for serious players and enthusiasts of poker

CREATE TABLE #output (
  DbName VARCHAR(MAX),
  ElectionId INT,
  PostId INT,
  CreationDate DATETIME,
  UserName VARCHAR(MAX),
  UserId INT,
  UserAccountId INT
);

DECLARE @query VARCHAR(MAX);

DECLARE dbcursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'tempdb', 'model' ,'msdb', 'Data.StackExchange');

DECLARE @DbName VARCHAR(MAX);

OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @query = '
    WITH Raw AS (
      SELECT
        LAG(CreationDate) OVER (ORDER BY CreationDate) AS PrevCrDate,
        Id,
        OwnerUserId AS UserId,
        CreationDate
      FROM [' + @dbname + '].dbo.PostsWithDeleted
      WHERE PostTypeId = 6
    )
    SELECT
      ''' + @dbname + ''' AS DbName,
      SUM(
        CASE
          WHEN
            PrevCrDate IS NULL
            OR
            DATEDIFF(day, Raw.PrevCrDate, Raw.CreationDate) > 30
          THEN
            1
          ELSE 0
        END
      ) OVER (
        ORDER BY Raw.CreationDate
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS ElectionId,
      Raw.Id AS PostId,
      Raw.CreationDate,
      Users.DisplayName AS UserName,
      Raw.UserId,
      Users.AccountId AS UserAccountId
    FROM Raw
    LEFT JOIN [' + @dbname + '].dbo.Users ON Raw.UserId = Users.Id
    ';
  INSERT INTO #output EXEC (@query);
  FETCH NEXT FROM dbcursor INTO @DbName;
END

CLOSE dbcursor;

/*
WITH Raw AS (
  SELECT
    SUM(HnqCount) AS HnqCount,
    MIN(UserName) AS UserName,
    SEAccountId
  FROM #output
  GROUP BY SEAccountId
)
SELECT
  DENSE_RANK() OVER (ORDER BY HnqCount DESC) AS Rank,
  UserName,
  SEAccountId,
  HnqCount,
  'http://stackexchange.com/users/'
    + CAST(SEAccountId AS VARCHAR(30))
    + '?tab=accounts|' + UserName AS 'SE Profile',
  'https://data.stackexchange.com/stackoverflow/query/678540?AccountId='
    + CAST(SEAccountId AS VARCHAR(30)) + '#resultSets|' + UserName
    AS [With Hidden Accounts]
FROM Raw
ORDER BY HnqCount DESC, SEAccountId;
*/

SELECT * FROM #output ORDER BY CreationDate;

Enter Parameters

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