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 researchers, developers, and users of Tor

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
),
-- only have posts with 5 tags
-- assumption is that these type of questions
-- have a major tag and a couple of minor ones
postsgt4 as (
   select ptgt4.postid
   from posttags ptgt4
   group by ptgt4.postid
   having count(*)> 4
)
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
inner join toptagsinCVQ tt on tt.tagid = mt.tagid
inner join postsgt4 p4 on p4.postid = pt.postid
where 
-- filter out the  top tags
pt.tagid not in ( select tagid from toptagsinCVQ)
-- what the CVQ room already cleared
and t.tagname not in (
'css' -- removed based on http://chat.stackoverflow.com/transcript/message/22798629#22798629
,'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'
,'menu'
,'ruby-on-rails'
,'scroll'
,'listview'
,'visual-studio'
,'android-activity'
,'java-ee'
,'algorithm'
,'server'
,'search'
,'video'
,'methods'
,'login'
,'csv'
,'sorting'
,'ajax'
,'html5'
,'wordpress'
)
group by t.tagname
having count(*) < 500
order by count(*) desc

view current version

edited may 23 15

created oct 19 14 rene