1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited dec 31

created dec 6 peterh - PLEASE DELETE THIS ACCOUNT