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.

Q&A for enthusiasts and scholars of mythology and folklore

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 <> 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], Tag, Score, [Post Count] FROM TopTags
WHERE [Top Tag] = 1
ORDER BY [User id], Score DESC

view current version

edited sep 25 20 João Eduardo Montandon

created aug 10 20 Martijn Pieters