Q&A 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