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 posts p inner join posttags pt on pt.postid = p.id where exists ( select 1 from pendingflags pf where pf.postid = p.id) 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 p.id from posts p inner join posttags pt on pt.postid = p.id group by p.id 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' ) group by t.tagname having count(*) < 500 order by 2 desc