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 SubTagness AS (
  SELECT
    P.TagId AS TagP,
    C.TagId AS TagC,
    1.0 * COUNT(*) / MIN(Tags.Count) AS Subtagness
  FROM PostTags P, PostTags C, Tags
  WHERE
    P.PostId = C.PostId
    AND
    P.TagId <> C.TagId
    AND
    Tags.Id = C.TagId
  GROUP BY
    P.TagId, C.TagId
),
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,
  Hin.TagId,
  Her.QUserId,
  Her.TagId

Enter Parameters

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