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.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH cw_filtered AS (
  SELECT
    p.OwnerUserId AS [User Id]
  , t.TagName AS Tag
  , p.Id
  , p.Score AS AnswerScore
  , 1 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.PostTypeId IN (1, 2)
    AND p.OwnerUserId IS NOT NULL
)
, by_post_count AS (
  SELECT
    [User Id]
  , Tag
  , COUNT(Id) As [Post Count]
  , SUM(AnswerScore) AS Score
  , RANK() OVER (PARTITION BY [User Id] 
                 ORDER BY SUM(Counted) DESC) AS post_count_rank
  FROM cw_filtered
  GROUP BY [User id], Tag
)
, is_considered AS (
    SELECT
      [User id]
    , Tag
    , Score
    , [Post Count]
    , CASE
        WHEN post_count_rank <= 9 THEN 1
        ELSE 0
      END AS [Considered]
    FROM by_post_count
)
, TopTags AS (
    SELECT
    RANK() OVER (
      PARTITION BY [User id], Considered ORDER BY Score DESC
    ) AS Rank
    , [User id]
    , Tag
    , Score
    , [Post Count]
    , Considered
    , CASE
        WHEN Considered = 1
         AND (
          RANK() OVER (
            PARTITION BY [User id], Considered ORDER BY Score DESC
          ) <= 6)
        THEN 1
        ELSE 0
      END AS [Top Tag]
    FROM is_considered
)

SELECT [User id], STRING_AGG(Tag, ',') AS Tags FROM TopTags
WHERE [Top Tag] = 1
GROUP BY [User id]

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results