Most active downvoters, relative to the site size (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 #Sites (
  DbId INT NOT NULL,
  DbName VARCHAR(MAX) NOT NULL,
  PostCount INT NOT NULL
);

CREATE TABLE #Users (
  UserId INT NOT NULL,
  SiteUserId INT NOT NULL,
  UserName VARCHAR(MAX) NOT NULL,
  DbId INT NOT NULL,
  Reputation INT NOT NULL,
  DownVotes INT NOT NULL
);

DECLARE @query1 VARCHAR(MAX) = '';
DECLARE @query2 VARCHAR(MAX) = '';

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

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

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

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @query1 <> ''
    SET @query1 = @query1 + '
    UNION ALL
    ';

  SELECT @query1 = @query1 + '
    SELECT
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      ''' + @DbName + ''' AS DbName,
      COUNT(*) AS PostCount
    FROM [' + @DbName + '].dbo.PostsWithDeleted
    WHERE PostTypeId BETWEEN 1 AND 2
  ';

  IF @query2 <> ''
    SET @query2 = @query2 + '
    UNION ALL
    ';

  SELECT @query2 = @query2 + '
    SELECT
      AccountId AS UserId,
      Id AS SiteUserId,
      DisplayName AS UserName,
      ' + CAST(@DbId AS VARCHAR(MAX)) + ' AS DbId,
      Reputation,
      DownVotes
    FROM [' + @DbName + '].dbo.Users
    WHERE
      DownVotes > 0
      AND
      Id > 0
      AND
      Reputation > 1
  ';

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

INSERT INTO #Sites EXEC(@query1);
INSERT INTO #Users EXEC(@query2);

SELECT TOP 100
  *,
  1.0 * #Users.DownVotes / #Sites.PostCount /*/ #Users.Reputation*/ AS Evilness
FROM #Sites, #Users
WHERE #Sites.DbId = #Users.DbId
ORDER BY Evilness DESC;

Enter Parameters

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