Tags that can be cleared of votes

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

Tor

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results