1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

CREATE TABLE #tmp (
  EditorUserId INT NOT NULL,
  EditedUserId INT NOT NULL,
  Count INT NOT NULL
);

WITH Raw AS (
  SELECT
    PostHistory.UserId AS EditorUserId,
    Posts.OwnerUserId AS EditedUserId,
    PostHistory.PostId
  FROM PostHistory, Posts
  WHERE
    PostHistory.PostId = Posts.Id
    AND
    PostHistory.PostHistoryTypeId <= 9
    AND
    PostHistory.UserId <> Posts.OwnerUserId
    AND
    PostHistory.UserId > 0
    AND
    Posts.OwnerUserId > 0
  GROUP BY PostHistory.UserId, Posts.OwnerUserId, PostHistory.PostId
)
INSERT INTO #tmp
SELECT EditorUserId, EditedUserId, COUNT(*) AS Count
FROM Raw
GROUP BY EditorUserId, EditedUserId;

CREATE UNIQUE INDEX idxA ON #tmp(EditorUserId, EditedUserId);
CREATE UNIQUE INDEX idxB ON #tmp(EditedUserId, EditorUserId);

SELECT TOP 1000
  'site://users/' + CAST(A.EditorUserId AS VARCHAR(MAX))
    + '|' + UserA.DisplayName AS UserA,
  'site://users/' + CAST(B.EditorUserId AS VARCHAR(MAX))
    + '|' + UserB.DisplayName AS UserB,
  A.Count AS CountA,
  B.Count AS CountB,
  CASE WHEN A.Count < B.Count THEN A.Count ELSE B.Count END
    AS MutualCount
FROM #tmp A, #tmp B, Users UserA, Users UserB
WHERE
  A.EditorUserId = B.EditedUserId
  AND
  A.EditedUserId = B.EditorUserId
  AND
  A.EditorUserId < B.EditorUserId
  AND
  UserA.Id = A.EditorUserId
  AND
  UserB.Id = B.EditorUserId
ORDER BY MutualCount DESC, A.EditorUserId, B.EditorUserId

fork query

edited jan 19 21

created jan 19 21 peterh (2)