Sites correlations by user rep

1

Please login or register to vote for this query.

(click on this box to dismiss)

Open Data

Q&A for developers and researchers interested in open data

CREATE TABLE #Accounts (
  DbId INT NOT NULL,
  UserId INT NOT NULL,
  Reputation 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'
  )
  AND
  (
  name = 'StackExchange.Meta'
  OR
  name NOT LIKE '%.Meta'
  );

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
      ''' + CAST(@DbId AS VARCHAR(MAX)) + ''' AS DbId,
      AccountId AS UserId,
      MAX(Reputation) AS Reputation
    FROM
      [' + @DbName + '].dbo.Users
    WHERE
      Id > 0
      AND
      Reputation >= 202
    GROUP BY AccountId
  ';

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

INSERT INTO #Accounts EXEC (@query);

WITH RepSq AS (
  SELECT DbId, SUM(CAST(Reputation AS BIGINT) * Reputation) AS RepSq
  FROM #Accounts
  GROUP BY DbId
),
RepSyn AS (
  SELECT
    A.DbId AS DbIdA,
    B.DbId AS DbIdB,
    SUM(CAST(A.Reputation AS BIGINT) * B.Reputation) AS Syn
  FROM #Accounts A, #Accounts B
  WHERE
    A.DbId < B.DbId
    AND
    A.UserId = B.UserId
  GROUP BY A.DbId, B.DbId
)
SELECT
  ADb.name AS DbNameA,
  BDb.name AS DbNameB,
  1.0 * RepSyn.Syn / SQRT(1.0 * A.RepSq * B.RepSq) AS Syn
FROM RepSyn, RepSq A, RepSq B, sysdatabases ADb, sysdatabases BDb
WHERE
  RepSyn.DbIdA = A.DbId
  AND
  RepSyn.DbIdB = B.DbId
  AND
  A.DbId = ADb.dbid
  AND
  B.DbId = BDb.dbId
ORDER BY syn DESC, A.DbId, B.DbId

Enter Parameters

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