CREATE TABLE #QAT ( QUserId INT NOT NULL, AUserId INT NOT...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Japanese Language

Q&A for students, teachers, and linguists wanting to discuss the finer points of the Japanese language

CREATE TABLE #QAT (
  QUserId INT NOT NULL,
  AUserId INT NOT NULL,
  TagId INT NOT NULL,
  Count INT NOT NULL
);

INSERT INTO #QAT
SELECT
  Q.OwnerUserId AS QUserId,
  A.OwnerUserId AS AUserId,
  PostTags.TagId AS TagId,
  COUNT(*) AS Count
FROM Posts Q, Posts A, PostTags
WHERE
  A.ParentId = Q.Id
  AND
  A.OwnerUserId > 0
  AND
  Q.OwnerUserId > 0
  AND
  Q.OwnerUserId <> A.OwnerUserId
  AND
  Q.Id = PostTags.PostId
GROUP BY Q.OwnerUserId, A.OwnerUserId, PostTags.TagId
HAVING COUNT(*) > 2;

SELECT COUNT(*) FROM #QAT;

/*
)
SELECT TOP 100
  A.QUserId AS User1,
  A.TagId AS Tag1,
  A.Count AS Count1,
  B.QUserId AS User2,
  B.TagId AS Tag2,
  B.Count AS Count2,
  CASE WHEN A.Count > B.Count THEN B.Count ELSE A.Count END AS Synergy
FROM Raw A, Raw B
WHERE
  A.QUserId = B.AUserId
  AND
  A.AUserId = B.QUserId
  AND
  A.TagId <> B.TagId
  AND
  A.QUserId < B.QUserId
ORDER BY Synergy DESC, A.QUserId, B.QUserId, A.TagId, B.TagId

Enter Parameters

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