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 speakers of other languages learning English

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

fork query

edited feb 3 17

created oct 19 14 rene