4

Please login or register to vote for this query.

(click on this box to dismiss)

Guide to how many zero upvoted answers are needed for the Tenacious and Unsung Hero badges. Self answers do not count. Answers must be 10 days old before they count towards the unscored total.

Q&A for students, teachers, polyglots, and anyone interested in the techniques of second-language acquisition

select
    count(a.Id) as [Accepted Answers],
    sum(case when q.OwnerUserId = a.OwnerUserId then 1 else 0 end) as [Self Answers],  
    sum(case when a.Score <> 0 and q.OwnerUserId != a.OwnerUserId then 1 else 0 end) as [Scored Answers],
    sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) < 10 then 1 else 0 end) as [Underage Unscored Answers],
    sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end) as [Unscored Answers],
    sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end)*1000 / sum(case when q.OwnerUserId != a.OwnerUserId then 1 else 0 end) / 10.0 as [Percentage Unscored],
    case
      when sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end) < 6 then
        6 - sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end)
      else
        ceiling(sum(case when q.OwnerUserId != a.OwnerUserId then 1 else 0 end)/5-sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end))/0.8
    end as [To Tenacious],
    case
      when sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end) < 11 then
        11 - sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end)
      else
        ceiling(sum(case when q.OwnerUserId != a.OwnerUserId then 1 else 0 end)/4-sum(case when a.Score = 0 and q.OwnerUserId != a.OwnerUserId and datediff(dd, a.CreationDate, getdate()) >= 10 then 1 else 0 end))/0.75
    end as [To Unsung Hero]
from
    Posts q
  inner join
    Posts a
  on a.Id = q.AcceptedAnswerId
where
      a.CommunityOwnedDate is null
  and a.OwnerUserId = ##UserId##
  and a.postTypeId = 2

fork query

edited may 29 15

created may 28 15 TobyLL