1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

declare @UserId integer = ##UserId##;

with
Top40Tag as (
select top 40 
         pt.TagId
       , count(1) tagPostCount 
       , row_number() over (order by count(1) desc) tagRank
  from PostTags pt 
 group by tagid 
 order by count(1) desc
)
,
UserRecord as (
select   pt.TagId
       , sum(p.score) TagAnswersScore
  from Posts p
       inner join PostTags pt on pt.PostId = p.ParentId
 where p.CommunityOwnedDate is null  -- Not community wiki
   and p.PostTypeID = 2              -- Answers
   and p.OwnerUserID = @UserId    -- By the specified user
 group by pt.TagId
)
,
GeneralistTrack as (
select   t.TagName
       , t40.tagPostCount
       , t40.tagRank
       , ur.TagAnswersScore
       , row_number() over (order by ur.TagAnswersScore desc, t40.tagRank) UserScoreRank
  from Top40Tag t40
       inner join Tags t on t.Id = t40.TagId
       left join UserRecord ur on ur.TagId = t40.TagId
)
select gt.*
       , case
           when TagAnswersScore >= 15 then 'hecho'
           when UserScoreRank <= 20 then 'esfuerzate!'
           else ''
         end TagStatus
  from GeneralistTrack gt
 order by gt.TagAnswersScore desc, tagRank

fork query

edited jun 5 23

created may 20 23 jachguate