Burninate priority list

3

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Stack Overflow

Q&A about the site for professional and enthusiast programmers

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
), 
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
),
questioncount as (
  select tagname
       , [count] as qcount
  from stackoverflow.dbo.tags 
)

select id as [Post Link]
     , 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
order by bp.score / sqrt(qc.qcount)

/*
select id as [Post Link]
  */  /* , title
     , 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  */
 /*    , (select [count] 
        from stackoverflow.dbo.tags 
        where tagname = case 
       when charindex('[', title) > 0 
       then substring(
              title, 
              charindex('[', title) + 1, 
              charindex(']', title, charindex('[', title)) - charindex('[', title) - 1
             )
       else '?'
       end collate sql_latin1_general_cp1_ci_ai ) as [Number of Questions] 
     , score
     , (select count(*)
        from votes 
        where postid = p.id
        and votetypeid = 2) as upvotes
     , (select count(*)
        from votes 
        where postid = p.id
        and votetypeid = 3) as downvotes
     , answercount
     , commentcount
     , favoritecount
     , owneruserid as [User Link]
     , 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
-- tunaki order by
order by score / sqrt(
      (select [count] 
       from stackoverflow.dbo.tags 
       where tagname = case 
       when charindex('[', title) > 0 
       then substring(
              title, 
              charindex('[', title) + 1, 
              charindex(']', title, charindex('[', title)) - charindex('[', title) - 1
             )
       else '?'
       end collate sql_latin1_general_cp1_ci_ai )) desc */

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results