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 ( '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' ) group by t.tagname having count(*) < 500 order by count(*) desc