Q&A about the site for information security professionals
DECLARE @UserId INT = ( CASE WHEN ##SiteAccountId:int?0## > 0 THEN ##SiteAccountId## ELSE ( SELECT Id FROM Users WHERE AccountId = ##NetworkAccountId:int?0## ) END ); CREATE TABLE #UserTagScores ( UserId INT NOT NULL, TagId INT NOT NULL, Score INT NOT NULL, PRIMARY KEY(UserId, TagId) ); CREATE TABLE #TagScorePct ( TagId INT NOT NULL, Score INT NOT NULL, Pct FLOAT NOT NULL, PRIMARY KEY(TagId, Score) ); DECLARE @Pcts TABLE ( Pct FLOAT NOT NULL PRIMARY KEY ); CREATE TABLE #Tags ( TagId INT NOT NULL PRIMARY KEY, YourScore INT NOT NULL ); CREATE TABLE #TagPctLimits ( TagId INT NOT NULL, Pct FLOAT NOT NULL, MinScore INT NOT NULL, PRIMARY KEY(TagId, Pct) ); INSERT INTO @Pcts SELECT 0.01 AS Pct UNION SELECT 0.05 UNION SELECT 0.1 UNION SELECT 0.2; INSERT INTO #UserTagScores SELECT Posts.OwnerUserId AS UserId, PostTags.TagId, SUM(Posts.Score) AS Score FROM Posts, PostTags, Tags WHERE Posts.ParentId = PostTags.PostId AND PostTags.TagId = Tags.Id AND Tags.Count >= 1000 AND Posts.CommunityOwnedDate IS NULL GROUP BY Posts.OwnerUserId, PostTags.TagId HAVING Posts.OwnerUserId > 0 AND COUNT(*) > 1; WITH TagScoreUserCountRaw AS ( SELECT TagId, Score, COUNT(*) AS UserCount FROM #UserTagScores GROUP BY TagId, Score ), TagScoreUserCount AS ( SELECT TagId, Score, UserCount, SUM(UserCount) OVER (PARTITION BY TagId ORDER BY Score DESC) AS CumCount FROM TagScoreUserCountRaw ), TagUserCount AS ( SELECT TagId, COUNT(*) AS UserCount FROM #UserTagScores GROUP BY TagId ) INSERT INTO #TagScorePct SELECT TagScoreUserCount.TagId, TagScoreUserCount.Score, 1.0 * TagScoreUserCount.CumCount / TagUserCount.UserCount AS Pct FROM TagScoreUserCount, TagUserCount WHERE TagScoreUserCount.TagId = TagUserCount.TagId; INSERT INTO #Tags SELECT T.TagId, COALESCE(#UserTagScores.Score, 0) AS YourScore FROM ( SELECT TagId FROM #TagScorePct GROUP BY TagId ) T LEFT JOIN #UserTagScores ON T.TagId = #UserTagScores.TagId AND #UserTagScores.UserId = @UserId; INSERT INTO #TagPctLimits SELECT #TagScorePct.TagId, Pcts.Pct, MIN(#TagScorePct.Score) AS Score FROM #TagScorePct, @Pcts Pcts WHERE #TagScorePct.Pct < Pcts.Pct GROUP BY #TagScorePct.TagId, Pcts.Pct; WITH Raw AS ( SELECT #TagPctLimits.TagId, #TagPctLimits.Pct, #TagPctLimits.MinScore, #Tags.YourScore FROM #TagPctLimits, #Tags WHERE #TagPctLimits.TagId = #Tags.TagId ), Raw2 AS ( SELECT TagId, MAX(Pct) AS Pct FROM Raw WHERE MinScore > YourScore GROUP BY TagId ), YACBT AS ( SELECT PostTags.TagId, COUNT(*) AS Count FROM PostTags, Posts A, Posts Q WHERE A.OwnerUserId = @UserId AND A.ParentId = Q.Id AND PostTags.PostId = Q.Id GROUP BY PostTags.TagId HAVING COUNT(*) > 1 ) SELECT Tags.TagName, Tags.Count AS TagCount, CAST(FLOOR(Raw.Pct * 100.0 + 0.5) AS VARCHAR(MAX)) + '%' AS Pct, Raw.MinScore, Raw.YourScore, MinScore - YourScore AS YouNeed, YACBT.Count AS AnswerCount, FLOOR(1.0 * YACBT.Count * Tags.Count / (MinScore - YourScore) / Raw.Pct) AS Fitness FROM Raw, Raw2, Tags, YACBT WHERE Tags.Count > 0 AND /*Raw.YourScore > 0 AND*/ Raw.TagId = Raw2.TagId AND Raw.Pct = Raw2.Pct AND Raw.TagId = Tags.Id AND YACBT.TagId = Tags.Id ORDER BY Fitness DESC, YouNeed, Tags.Id