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 linguists, teachers, students and Spanish language enthusiasts in general wanting to discuss the finer points of the language

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

view current version

edited feb 20 15

created oct 19 14 rene