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 administrators and users of the CiviCRM Constituent Relationship Management software
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 'latex' as tagname, 1337 as [Number of Posts] union select 'scrape', 1336 union select 'scraper', 1335 union select 'screen-scraping', 1334 union select 'web-scraping', 1333 union select 'sources', 1332 union select 'code-smell', 1331 */ select 'software-engineering', 1337 as [Number of posts] -- union select 'blinking', 1336 union 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' ,'json' -- room tags ,'third-party' ,'scrape' ,'scraper' ,'screen-scraping' ,'web-scraping' ,'sources' ,'code-smell' ,'database' ,'mysql' ,'twitter-bootstrap ' ,'css3' ,'database' ,'asp.net' ,'sql' ,'.net' ,'image' ,'forms' ,'android-studio' , 'php' ,'priority' , 'blinking' ,'web2.0' ,'python-3.x' ,'selenium' ,'godaddy' , 'd3.js' ,'android-fragments' , 'dynamic' ,'c' ,'opencv' ,'software-engineering' ) group by t.tagname having count(*) < 500 order by 2 desc