1

Please login or register to vote for this query.

(click on this box to dismiss)

Users with the highest entropy of posts among sites. (i.e. users by the count of the posts network-wide, overwheighting the ones having a lot of posts on a lot of different sites).

Q&A about the site for engineers, producers, editors, and enthusiasts spanning the fields of video, and media creation

DECLARE @LogFact TABLE (
  N INT NOT NULL,
  LogFact FLOAT NOT NULL,
  PRIMARY KEY(N)
);

DECLARE @n INT = 1
DECLARE @logSum FLOAT = 0

WHILE @n < 100000
BEGIN
  INSERT INTO @LogFact VALUES(@n, @logSum)
  SET @n = @n + 1
  SET @logSum = @logSum + LOG(@n)
END;

CREATE TABLE #Accounts (
  UserId INT NOT NULL,
  DbId INT NOT NULL,
  Count INT NOT NULL,
  PRIMARY KEY(UserId, DbId)
);

DECLARE @query 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 @query <> ''
    SET @query = @query + '
    UNION ALL
    ';

  SELECT @query = @query + '
    SELECT
      Users.AccountId AS UserId,
      ''' + CAST(@DbId AS VARCHAR(MAX)) + ''' AS DbId,
      COUNT(*) AS Count
    FROM
      [' + @DbName + '].dbo.Users,
      [' + @DbName + '].dbo.Posts
    WHERE
      Posts.OwnerUserId = Users.Id
      AND
      Users.Id > 0
      AND
      Posts.PostTypeId = 2
    GROUP BY Users.AccountId
  ';

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

INSERT INTO #Accounts EXEC (@query);

WITH Sums AS (
  SELECT UserId, SUM(Count) AS Count
  FROM #Accounts
  GROUP BY UserId
  HAVING COUNT(*) > 1
)
SELECT TOP 10000
  'https://stackexchange.com/users/'
    + CAST(Sums.UserId AS VARCHAR(MAX))
    + '?tab=accounts|' + CAST(Sums.UserId AS VARCHAR(MAX))
    AS [Accounts Link],
  'https://data.stackexchange.com/stackoverflow/query/1089480'
    + '?NetworkAccountId=' + CAST(Sums.UserId AS VARCHAR(MAX))
    + '|' + CAST(Sums.UserId AS VARCHAR(MAX))
    AS [With Hidden Accounts],
  MIN(Sums.Count) AS Count,
  MIN(SumFact.LogFact) - SUM(SiteFact.LogFact) AS LogEntropy
FROM Sums, #Accounts, @LogFact SumFact, @LogFact SiteFact
WHERE
  Sums.UserId = #Accounts.UserId
  AND
  Sums.Count = SumFact.N
  AND
  #Accounts.Count = SiteFact.N
GROUP BY Sums.UserId
ORDER BY LogEntropy DESC, Sums.UserId

fork query

edited may 4

created may 4 peterh - PLEASE DELETE THIS ACCOUNT