Symbiots

1

Please login or register to vote for this query.

(click on this box to dismiss)

User1 answered a lot of question of User2 in tag1, while User2 answered a lot of question of User1 in tag2. tag1 <> tag2.

Blender Meta

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

WITH Raw AS (
  SELECT
    Q.OwnerUserId AS QUserId,
    A.OwnerUserId AS AUserId,
    PostTags.TagId,
    COUNT(*) AS Count
  FROM Posts Q, Posts A, PostTags
  WHERE
    A.ParentId = Q.Id
    AND
    PostTags.PostId = Q.Id
    AND
    Q.OwnerUserId > 0
    AND
    A.OwnerUserId > 0
    AND
    Q.OwnerUserId <> A.OwnerUserId
  GROUP BY
    Q.OwnerUserId,
    A.OwnerUserId,
    PostTags.TagId
  HAVING
    COUNT(*) > 1
),
Raw2 AS (
  SELECT TOP ##TopN:int?1000##
    Hin.QUserId AS HinQUserId,
    Her.QUserId AS HerQUserId,
    MAX(CASE WHEN Hin.Count > Her.Count THEN Her.Count ELSE Hin.Count END)
      AS MaxSynergy
  FROM Raw Hin, Raw Her
  WHERE
    Hin.QUserId = Her.AUserId
    AND
    Hin.AUserId = Her.QUserId
    AND
    Hin.TagId <> Her.TagId
  GROUP BY
    Hin.QUserId, Her.QUserId
  ORDER BY MaxSynergy DESC
)
SELECT
  Hin.QUserId AS [User Link],
  HinTag.TagName,
  Hin.Count,
  Her.QUserId AS [User Link],
  HerTag.TagName,
  Her.Count
FROM Raw2, Raw Hin, Raw Her, Tags HinTag, Tags HerTag
WHERE
  Raw2.HinQUserId = Hin.QUserId
  AND
  Raw2.HinQUserId = Her.AUserId
  AND
  Raw2.HerQUserId = Hin.AUserId
  AND
  Raw2.HerQUserId = Her.QUserId
  AND
  Hin.Count >= Raw2.MaxSynergy
  AND
  Her.Count >= Raw2.MaxSynergy
  AND
  HinTag.Id = Hin.TagId
  AND
  HerTag.Id = Her.TagId
  AND
  Hin.TagId <> Her.TagId
  AND
  Hin.QUserId < Her.QUserId
ORDER BY
  Raw2.MaxSynergy DESC,
  Hin.QUserId + Her.QUserId,
  Hin.QUserId,
  Her.QUserId

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results