User1 answered a lot of question of User2 in tag1, while User2 answered a lot of question of User1 in tag2. tag1 <> tag2.
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