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
),
Raw2 AS (
  SELECT
    Hin.QUserId AS HinQUserId,
    Her.QUserId AS HerQUserId,
    MAX(Hin.Count) AS HinMaxCount,
    MAX(Her.Count) AS HerMaxCount
  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
)
SELECT TOP 1000 * FROM Raw;
/*
SELECT TOP 1000
  Hin.QUserId,
  Hin.TagId,
  Hin.Count,
  Her.QUserId,
  Her.TagId,
  Her.Count,
  CASE WHEN Hin.Count > Her.Count THEN Her.Count ELSE Hin.Count END
    AS Synergy
FROM Raw Hin, Raw Her, Raw2
WHERE
  Hin.QUserId = Raw2.HinQUserId
  AND
  Hin.AUserId = Raw2.HerQUserId
  AND
  Her.QUserId = Raw2.HerQUserId
  AND
  Hin.AUserId = Raw2.HinQUserId
  AND
  Hin.Count = Raw2.HinMaxCount
  AND
  Her.Count = Raw2.HerMaxCount
ORDER BY Synergy DESC, Hin.QUserId, Her.QUserId

Enter Parameters

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