Modified to left Join Journey to be a Generalist2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

-- Modified to left Join Journey to be a Generalist2

 
DECLARE @UserID int = ##UserId##

;with top40 as (
  select top 40 t.id, t.tagname, count(pt.postid) K
  from tags t 
     inner join posttags pt
     on pt.tagid = t.id
  
  group by t.tagname, t.id
  order by count(pt.postid) desc
),
  
myanswers as(
  select p.parentid, p.score
  from posts p
  where
    p.owneruserid =  @UserID  and
    p.communityowneddate is null
)

select t40.tagname as 'Tag', sum(p1.score) as 'Score',
case when sum(p1.score) >= 15 then ':-)' else ':-(' end as 'Status'
from 
top40 t40 
INNER JOIN posttags pt1
ON pt1.tagid = t40.id 

LEFT JOIN  myanswers p1
ON pt1.postid = p1.parentid

  



  
group by t40.tagname, t40.K 
order by t40.K desc

created may 11 11