Your brothers

1

Please login or register to vote for this query.

(click on this box to dismiss)

Users whose tag score distribution is the most similar to yours

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

CREATE TABLE #UserTagScore (
  UserId INT,
  TagId INT,
  Score BIGINT,
  PRIMARY KEY(UserId, TagId)
);

INSERT INTO #UserTagScore
SELECT A.OwnerUserId AS UserId, PostTags.TagId, SUM(A.Score) AS Score
FROM Posts Q, Posts A, PostTags
WHERE
  A.OwnerUserId > 0
  AND
  A.ParentId = Q.Id
  AND
  Q.Id = PostTags.PostId
GROUP BY A.OwnerUserId, PostTags.TagId
HAVING SUM(A.Score) > 0;

DECLARE @UserScoreSq TABLE(
  UserId INT,
  ScoreSq BIGINT,
  PRIMARY KEY(UserId)
);

INSERT INTO @UserScoreSq
SELECT
  UserId, SUM(Score * Score) AS ScoreSq
FROM #UserTagScore
GROUP BY UserId;

DECLARE @YourScoreSq FLOAT;

SELECT @YourScoreSq = ScoreSq
FROM @UserScoreSq
WHERE UserId = ##SiteAccountId:int##;

SELECT TOP 100
  Brother.UserId AS [User Link],
  1.0 * SUM(1.0 * You.Score * Brother.Score)
    / SQRT(MIN(BrotherScoreSq.ScoreSq)) / SQRT(@YourScoreSq)
    AS SimilarityRaw
FROM #UserTagScore You, #UserTagScore Brother, @UserScoreSq BrotherScoreSq
WHERE
  You.UserId = ##SiteAccountId##
  AND
  You.TagId = Brother.TagId
  AND
  BrotherScoreSq.UserId = Brother.UserId
GROUP BY Brother.UserId
ORDER BY SimilarityRaw DESC, Brother.UserId

Enter Parameters

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