3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people looking to bypass life's everyday problems with simple tricks

-- all questions that qualify for burnination
with burninatePosts as 
(
select id 
     , (case 
       when charindex('[', title) > 0 
       then substring(
              title, 
              charindex('[', title) + 1, 
              charindex(']', title, charindex('[', title)) - charindex('[', title) - 1
             )
       else '?'
       end) as maintag
    /* debugging ...
     , charindex('[', title)
     , charindex(']', title, charindex('[', title)) 
     , case 
       when charindex('[', title) > 0 
       then substring(
              title, 
              charindex('[', title) + 1, 
              charindex(']', title, charindex('[', title)) - charindex('[', title) - 1
             )
       else '?'
       end  */
     , score
     , answercount
     , commentcount
     , favoritecount
     , owneruserid
     , lastactivitydate
     , creationdate
from posts p
where p.closeddate is null
and acceptedanswerid is null
and tags like '%<burninate-request>%'
and tags not like '%<status-%'
and score > 20
), 
-- CTE for the up and down vote split for a burninate post
updownvotes as (
  select postid
  , sum(case when votetypeid = 2 then 1 else 0 end ) as upvotes
       , sum(case when votetypeid = 3 then 1 else 0 end ) as downvotes
  from votes
  where votetypeid in (2,3)
  group by postid
),
-- CTE to find the question count for a tag
questioncount as (
  select t.tagname
       , t.[count] as qcount
       , (select count(*)
          from stackoverflow.dbo.posts p
          inner join stackoverflow.dbo.posttags pt on pt.postid = p.id
          where pt.tagid = t.id
          and p.answercount = 0) as  unacount
  from stackoverflow.dbo.tags t
)

-- final query
select id as [Post Link]
     , qc.qcount as [Question Count]
     , unacount
     , score
     , upvotes
     , downvotes
     , answercount
     , commentcount
     , favoritecount
     , owneruserid as [User Link]
     , lastactivitydate
     , creationdate
from burninateposts bp 
inner join updownvotes v 
        on v.postid = bp.id
left outer join questioncount qc 
             on qc.tagname = bp.maintag collate sql_latin1_general_cp1_ci_ai
 -- what does this need to be?
 -- Tunaki factor
 order by bp.score / sqrt(qc.qcount)  * (1 / (((coalesce(unacount,1)/coalesce(qcount,1))*100) +1)) desc

view current version

edited jun 6 16

created jun 1 16 rene