1

Please login or register to vote for this query.

(click on this box to dismiss)

http://meta.stackoverflow.com/q/82233/156011

Q&A for programmers

-- Badge request - 'Ignored' (unsung hero #2)
-- http://meta.stackoverflow.com/q/82233/156011

select
  owneruserid as [User Link],
  owneruserid,
  count(*) IgnoredCount,
  count(case when count(*)>=  50 then 1 end) over () SilverBadge,
  count(case when count(*)>= 100 then 1 end) over () GoldBadge
from
(
select
  a.id,
  a.owneruserid,
  rnk = rank() over (partition by q.id order by a.score desc)
from posts a
inner join posts q on q.id = a.parentid and q.posttypeid = 1 -- question
where a.posttypeid = 2              -- answer
  and a.score >= 5                  -- 5 or more upvotes
  and q.acceptedanswerid is null    -- no accepted answer
  and q.communityowneddate is null  -- Question is not a community wiki
  and a.communityowneddate is null  -- Answer is not a community wiki
  and q.creationdate < dateadd(d, -30, getutcdate())  -- after 30 days
  and a.owneruserid is not null
) X
where rnk=1
group by owneruserid
having count(*) >= 10
order by IgnoredCount desc

-- Having 10, 50, 300 answers with:
-- The highest voted answer on a question

fork query

created mar 9 11 cyberkiwi2010