3

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional and enthusiast programmers

-- all questions that qualify for burnination
with burninatePosts as 
(
select id 
     -- this case forces a tag for posts without a default/standard title
     , case 
       when id = 278455 then 'coding-style'
       when id = 260218 then 'movement'  
       when id = 289771 then 'milliseconds' 
       when id = 260214 then 'save' 
       when id = 253502 then 'phone' 
       when id = 268173 then 'width' 
       when id = 314344 then 'dup' 
       when id = 250913 then 'redraw' 
       when id = 254210 then 'language-design'
       when id = 296857 then 'angular-ui'
       else 
       -- grab tag from title
       (case 
       when charindex('[', title) > 0 
       then substring(
              title, 
              charindex('[', title) + 1, 
              charindex(']', title, charindex('[', title)) - charindex('[', title) - 1
             )
       else '?'
       end) 
       end as maintag
     , title
    /* 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]
, '[' + coalesce(lower(maintag), title) +'](http://meta.stackoverflow.com/q/'+cast(id as nvarchar)+')   ' [Link]
     , maintag
     , id
     , qc.qcount as [SO Question Count]
     , unacount as [SO Unanswered Count]
     , 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
where qc.qcount > 50 
or    qc.qcount is null
 -- 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

fork query

edited sep 20 17

created jun 1 16 rene