See https://meta.stackexchange.com/q/329452/148310 1) The input is a comma-separated list of user ID's. 2) The query finds each user's top 3 tags. 3) Then compares the specified users across all the tags found in step 2.
Q&A about the site for serious players and enthusiasts of poker
CREATE TABLE #TopTagsPerSelectUsers ( UserId INT , UserName NVARCHAR(128) -- Schema check was 40 , TagId INT , TagName NVARCHAR(128) -- Schema check was 35 , tagScore INT , numPosts INT ); DECLARE @queryStr AS NVARCHAR (max); WITH targetUsers AS ( SELECT u.Id AS UserId FROM Users u WHERE u.Id IN ( SELECT DISTINCT CAST (TRIM (value) AS INT) FROM STRING_SPLIT (##CSVOfUsers:STRING##, ',') -- Underscores not allowed in SEDE names WHERE TRIM (value) != '' ) ), eligiblePosts AS ( SELECT tu.UserId -- "Top Tags" *Score* only counts answers... , Score = ( CASE WHEN p.PostTypeId = 2 AND p.CommunityOwnedDate IS NULL THEN p.Score ELSE 0 END ) , parentQ = ( CASE WHEN p.PostTypeId = 2 THEN p.ParentId ELSE p.Id END ) , postId = p.Id FROM targetUsers tu INNER JOIN Posts p ON p.OwnerUserId = tu.UserId WHERE p.PostTypeId IN (1, 2) -- Q&A only ), rankedTagsPerUser AS ( SELECT ep.UserId , pt.TagId , tagScore = SUM (ep.Score) , numPosts = COUNT (ep.postId) , Rank = DENSE_RANK() OVER ( PARTITION BY ep.UserId ORDER BY SUM (ep.Score) DESC, COUNT (ep.postId) DESC ) FROM eligiblePosts ep INNER JOIN PostTags pt ON pt.PostId = ep.parentQ GROUP BY ep.UserId, pt.TagId ), distinctTags AS ( SELECT DISTINCT tpu.TagId FROM rankedTagsPerUser tpu WHERE tpu.Rank <= 3 ) INSERT INTO #TopTagsPerSelectUsers SELECT tpu.UserId , u.DisplayName , dt.TagId , t.TagName , tpu.tagScore , tpu.numPosts FROM distinctTags dt INNER JOIN rankedTagsPerUser tpu ON tpu.TagId = dt.TagId INNER JOIN Tags t ON t.Id = tpu.TagId INNER JOIN Users u ON u.Id = tpu.UserId ; ;WITH uniqueTagSet AS ( SELECT ttpu.TagId , ttpu.TagName , tagMaxScore = MAX (ttpu.tagScore) , tagMaxPosts = MAX (ttpu.numPosts) FROM #TopTagsPerSelectUsers ttpu GROUP BY ttpu.TagId, ttpu.TagName ), tagColmnNames AS ( SELECT tagIds = STUFF ( ( SELECT N', ' + QUOTENAME (CAST (uts.TagId AS NVARCHAR) ) FROM uniqueTagSet uts ORDER BY uts.tagMaxScore DESC, uts.tagMaxPosts DESC, uts.TagName FOR XML PATH (N''), TYPE ).value (N'.[1]', N'nvarchar(max)'), 1, 2, N'' ) , tagColCode = STUFF ( ( SELECT N', ISNULL ([' + CAST (uts.TagId AS NVARCHAR) + '], 0) AS ' + QUOTENAME (uts.TagName) FROM uniqueTagSet uts ORDER BY uts.tagMaxScore DESC, uts.tagMaxPosts DESC, uts.TagName FOR XML PATH (N''), TYPE ).value (N'.[1]', N'nvarchar(max)'), 1, 2, N'' ) ) SELECT @queryStr = N' SELECT [User] = UserLink , [Max Tag Score] = UserRank , [Category] = ''Scores'' , ' + tcn.tagColCode + N' FROM ( SELECT UserLink = ''site://users/'' + CAST(ttpu.UserId AS nvarchar(128)) + ''|'' + ttpu.UserName , UserRank = (SELECT MAX (ur.tagScore) FROM #TopTagsPerSelectUsers ur WHERE ur.UserId = ttpu.UserId) , ttpu.tagScore , ttpu.TagId , ttpu.UserName FROM #TopTagsPerSelectUsers ttpu ) foo PIVOT ( SUM (tagScore) -- IsNull and Coalesce do not seem to work here. FOR TagId IN (' + tcn.tagIds + ') ) pvt UNION ALL SELECT [User] = UserLink , [Max Tag Score] = UserRank , [Category] = ''Num Posts'' , ' + tcn.tagColCode + N' FROM ( SELECT UserLink = ''site://users/'' + CAST(ttpu.UserId AS nvarchar(128)) + ''|'' + ttpu.UserName , UserRank = (SELECT MAX (ur.tagScore) FROM #TopTagsPerSelectUsers ur WHERE ur.UserId = ttpu.UserId) , ttpu.numPosts , ttpu.TagId , ttpu.UserName FROM #TopTagsPerSelectUsers ttpu ) foo PIVOT ( SUM (numPosts) -- IsNull and Coalesce do not seem to work here. FOR TagId IN (' + tcn.tagIds + ') ) pvt ORDER BY [Max Tag Score] DESC, [User], [Category] DESC ' FROM tagColmnNames tcn EXEC sp_executesql @queryStr DROP TABLE #TopTagsPerSelectUsers