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