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.

History of Science and Math

Q&A for people interested in the history and origins of science and mathematics

WITH by_post_count AS (
  SELECT
    t.TagName AS Tag
  , COUNT(p.Id) AS [Post Count]
  , SUM(
      CASE
        -- Tag score is the sum of non-CW answer scores for the tag
        WHEN (p.PostTypeId = 2 AND p.CommunityOwnedDate IS NULL) THEN p.Score
        ELSE 0
      END
    ) AS Score
  , RANK() OVER (ORDER BY COUNT(p.Id) DESC) AS post_count_rank
  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)
  AND (
    -- Set to 1 to include Community Wiki posts from the post counts
    -- and scoring.
    ##countcw:int?0## != 0
    OR p.CommunityOwnedDate IS NULL
  )
  GROUP BY t.TagName
),
is_considered AS (
    SELECT
      Tag
    , Score
    , [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, [Post Count] DESC)
, Tag
, Score
, [Post Count]
, Considered
, CASE
    WHEN Considered = 1
     AND (RANK() OVER (PARTITION BY Considered ORDER BY Score DESC) <= ##toptagcount:int?6##)
    THEN 1
    ELSE 0
  END AS [Top Tag]
FROM is_considered
ORDER BY Considered DESC, Score DESC, [Post Count] DESC;

Enter Parameters

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