Determine what Top Tags are shown on a user profile

0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the tag scores and per-tag post counts for a given user, with the top 9 tags _by non-CW post count_ selected for showing on the profile page, then ordered by tag score. The top six of these actually make it to the profile page. There are parameters to alter the post-count cutoff point and a flag to enable counting Community Wiki posts in the per-tag post counts. The defaults for these are what is actually used, but playing with these settings can help understanding the algorithm better.

Amateur Radio

Q&A for amateur radio enthusiasts

WITH cw_filtered AS (
  SELECT
    t.TagName AS Tag
  , p.Id
  , CASE
      -- Tag score is the sum of non-CW answer scores for the tag
      WHEN (PostTypeId = 2 AND p.CommunityOwnedDate IS NULL) THEN p.Score
      ELSE 0
    END AS AnswerScore
  , CASE
      -- Set to 1 to include Community Wiki posts in the tag post counts.
      WHEN ##countcw:int?0## != 0 THEN 1
      ELSE CASE WHEN p.CommunityOwnedDate IS NULL THEN 1 ELSE 0 END
  END AS Counted
  FROM Posts p
  -- If post is answer get tags via associated question, otherwise directly
  INNER JOIN PostTags pt ON pt.PostId = COALESCE(p.ParentId, p.Id)
  INNER JOIN Tags t      ON pt.TagId = t.Id
  WHERE
      p.OwnerUserId = ##userid:int##
  AND p.PostTypeId IN (1, 2)
)
, by_post_count AS (
  SELECT
    Tag
  , COUNT(Id) As [Post Count]
  , SUM(Counted) AS [Tag Post Count]
  , SUM(AnswerScore) AS Score
  , RANK() OVER (ORDER BY SUM(Counted) DESC) AS post_count_rank
  FROM cw_filtered
  GROUP BY Tag
)
, is_considered AS (
    SELECT
      Tag
    , Score
    , [Post Count]
    , [Tag Post Count]
    , CASE
        WHEN post_count_rank <= ##cutoff:int?9## THEN 1
        ELSE 0
      END AS [Considered]
    FROM by_post_count
)
SELECT
  RANK() OVER (
    PARTITION BY Considered ORDER BY Score DESC, [Tag Post Count] DESC
  )
, Tag
, Score
, [Post Count]
, [Tag Post Count]
, Considered
, CASE
    WHEN Considered = 1
     AND (
      RANK() OVER (
        PARTITION BY Considered ORDER BY Score DESC, [Tag Post Count] DESC
      ) <= ##toptagcount:int?6##)
    THEN 1
    ELSE 0
  END AS [Top Tag]
FROM is_considered
ORDER BY Considered DESC, Score DESC, [Tag Post Count] DESC;

Enter Parameters

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