1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for people who use Blender to create 3D graphics, animations, or games

DECLARE @SiteAccountId INT;
SELECT @SiteAccountId = Id
FROM Users
WHERE AccountId = ##NetworkAccountId:int##;

CREATE TABLE #Scores (
  UserId INT NOT NULL,
  TagId INT NOT NULL,
  Score BIGINT NOT NULL,
  PRIMARY KEY(UserId, TagId)
);

INSERT INTO #Scores
SELECT
  Posts.OwnerUserId AS UserId,
  PostTags.TagId,
  SUM(5 - 2 * Votes.VoteTypeId) AS Score
FROM Posts, Votes, PostTags
WHERE
  Votes.PostId = Posts.Id
  AND
  Votes.VoteTypeId BETWEEN 2 AND 3
  AND
  Posts.PostTypeId = 2
  AND
  Posts.ParentId = PostTags.PostId
  AND
  Posts.OwnerUserId > 0
GROUP BY Posts.OwnerUserId, PostTags.TagId
HAVING SUM(5 - 2 * Votes.VoteTypeId) <> 0;

DECLARE @YourSqrt FLOAT;
SELECT @YourSqrt = SQRT(SUM(Score * Score))
FROM #Scores
WHERE UserId = @SiteAccountId;

WITH AB AS (
  SELECT
    Other.UserId,
    SUM(You.Score*Other.Score) AS AB
  FROM #Scores You, #Scores Other
  WHERE
    You.UserId = @SiteAccountId
    AND
    You.TagId = Other.TagId
  GROUP BY Other.UserId
),
AA AS (
  SELECT
    UserId,
    SQRT(SUM(Score*Score)) AS AA
  FROM #Scores
  GROUP BY UserId
)
SELECT TOP 100
  AA.UserId AS [User Link],
  1.0 * AB.AB / AA.AA / @YourSqrt
    AS Correlation
FROM AA, AB, Users
WHERE
  AA.UserId = AB.UserId
  AND
  AA.UserId = Users.Id
  AND
  Users.Reputation >= ##MinRep:int?1000##
ORDER BY Correlation DESC

fork query

edited apr 30

created apr 30 peterh - PLEASE DELETE THIS ACCOUNT