6

Please login or register to vote for this query.

(click on this box to dismiss)

This query list tags and it's number of posts in the CVQ with the following consditions: - Not in the top 10 of questions with CV's - tag is used on questions with 5 other tags - tag is used on questions in the top 10 with CV

Q&A for scientists, academics, teachers, and students in the field of chemistry

with toptagsinCVQ as (
    select top 10 
           pt.tagid -- ,count(*) 
    from  posttags pt 
    inner join pendingflags pf on pf.postid = pt.postid
    group by pt.tagid
    order by count(*) desc
)
select t.tagname 
     , count(*) as [Number of Posts]
from posts p
inner join posttags pt on pt.postid = p.id
inner join posttags mt on mt.postid = p.id
inner join pendingflags pf on pf.postid = p.id
inner join tags m on m.id = mt.tagid
inner join tags t on t.id = pt.tagid and t.id <> m.id
where 
-- which major tags...
mt.tagid in ( select tagid from toptagsinCVQ)
-- only have posts with 5 tags
-- assumption is that these type of questions
-- have a major tag and a couple of minor ones
and p.id in (select pt.postid
   from posttags pt
   group by pt.postid
   having count(*)> 4)
-- filter out the  top tags
and pt.tagid not in ( select tagid from toptagsinCVQ)
-- what the CVQ room already cleared
and t.tagname not in (
'arrays'
,'swing'
,'string'
,'xml'
,'eclipse'
,'google-chrome'
,'asp.net-mvc'
,'web'
,'internet-explorer'
,'ios'
,'angularjs'
,'apache'
,'jsp'
,'function'
,'performance'
,'web-services'
,'iframe'
,'spring'
,'dom'
,'class'
,'node.js'
,'table'
,'list'
,'jquery-ui'
,'button'
,'google-maps'
,'firefox'
,'svg'
,'linux'
,'multithreading'
,'mysqli'
,'responsive-design'
,'post'
,'wpf'
,'windows'
,'email'
,'input'
,'object'
,'regex'
,'loops'
,'cordova'
,'mobile'
,'sql-server'
,'api'
,'sockets'
,'file'
,'codeigniter'
,'pdo'
,'security'
,'python-2.7'
,'validation'
,'variables'
,'user-interface'
,'animation'
,'asp.net-mvc-4'
,'winforms'
,'c++'
,'parsing'
,'browser'
,'jquery-mobile'
,'sqlite'
,'session'
,'website'
,'checkbox'
,'google-maps-api-3'
,'facebook'
,'rest'
,'phpmyadmin'
,'laravel'
,'servlets'
,'hibernate'
,'canvas'
,'web-applications'
,'date'
,'django'
,'url'
,'select'
,'encryption'
,'hyperlink'
,'oop'
,'if-statement'
,'arraylist'
,'pdf'
,'twitter-bootstrap-3'
,'text'
,'spring-mvc'
,'.htaccess'
,'for-loop'
,'http'
)
group by t.tagname
having count(*) < 500
order by 2 desc  OPTION (OPTIMIZE FOR UNKNOWN)

view current version

edited feb 21 15

created oct 19 14 rene