0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for committed Christians, experts in Christianity and those interested in learning more

WITH 
  cw_filtered AS (
  SELECT
    t.TagName AS Tag
  , p.OwnerUserId AS userid
  , 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
    userid
  , 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, userid
)

, is_considered AS (
    SELECT
      userid
    , 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
)
, topTags AS (
  SELECT
    userid
  , 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
)  

, results AS (
  SELECT TOP 6 userid, Tag, Score FROM topTags
  ORDER BY [Top Tag] DESC, Score DESC, [Tag Post Count] DESC
)


SELECT
            u.id, u.reputation, u.creationdate, u.displayname, u.lastaccessdate, u.websiteurl, u.location, u.aboutme, u.views, u.upvotes, u.downvotes, u.profileimageurl, u.emailhash, u.accountid,
            (   
                SELECT          COUNT(p.owneruserid)
                FROM            posts p        
                WHERE           p.OwnerUserId = u.id AND p.PostTypeId in (1,2)
            ),
            
            (   
                SELECT TOP 1    r.Tag
                FROM            results r
                WHERE           r.userid = u.id
                ORDER BY        r.Score DESC
            ) AS [top_tag_1],

            (   SELECT TOP 1    t.Tag
                FROM (
                  SELECT TOP 2  *
                  FROM          results tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.Score DESC
                ) t
                ORDER BY t.Score
            ) AS [top_tag_2],
           
            (   SELECT TOP 1    t.Tag
                FROM (
                  SELECT TOP 3  *
                  FROM          results tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.Score DESC
                ) t
                ORDER BY t.Score
            ) AS [top_tag_3],
            
            (   SELECT TOP 1    t.Tag
                FROM (
                  SELECT TOP 4  *
                  FROM          results tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.Score DESC
                ) t
                ORDER BY t.Score
            ) AS [top_tag_4],
            
            (   SELECT TOP 1    t.Tag
                FROM (
                  SELECT TOP 5  *
                  FROM          results tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.Score DESC
                ) t
                ORDER BY t.Score
            ) AS [top_tag_5],
            
            (   SELECT TOP 1    t.Tag
                FROM (
                  SELECT TOP 6  *
                  FROM          results tu
                  WHERE         tu.userid = u.id 
                  ORDER BY      tu.Score DESC
                ) t
                ORDER BY t.Score
            ) AS [top_tag_6]
            
    
FROM        users u
WHERE       u.id = 3315

view current version

edited aug 15

created aug 15 jon.doe33734