0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

declare @UserId int = ##UserID##;

with
AcceptedAnswers as (
select a.OwnerUserId, count(1) AcceptedAnswersCount
  from Posts q
       inner join Posts a on a.Id = q.AcceptedAnswerId
 where q.PostTypeId = 1
   and q.CommunityOwnedDate is null
   and q.DeletionDate is  null
   and a.CommunityOwnedDate is null
 group by a.OwnerUserId
)
,
GeneralAnswers as (
select a.OwnerUserId, count(1) PostedAnswersCount
  from Posts a
 where a.CommunityOwnedDate is null
   and a.DeletionDate is null
 group by a.OwnerUserId
)
,
SpecificUser as (
select   0 Orden
       , u.Id
       , u.DisplayName
       , ga.PostedAnswersCount
       , aa.AcceptedAnswersCount
       , round((aa.AcceptedAnswersCount * 100.0) / ga.PostedAnswersCount, 2) AcceptPercent
  from Users u
       inner join GeneralAnswers ga on ga.OwnerUserId = u.Id
       inner join AcceptedAnswers aa on aa.OwnerUserId = u.Id
 where u.Id = @UserId
)
,
TopUsers as (
select  top 100
         row_number() over (order by (aa.AcceptedAnswersCount * 100.0) / ga.PostedAnswersCount desc) Orden
       , u.Id
       , u.DisplayName
       , ga.PostedAnswersCount
       , aa.AcceptedAnswersCount
       , round((aa.AcceptedAnswersCount * 100.0) / ga.PostedAnswersCount, 2) AcceptPercent
  from Users u
       inner join GeneralAnswers ga on ga.OwnerUserId = u.Id
       inner join AcceptedAnswers aa on aa.OwnerUserId = u.Id
 where ga.PostedAnswersCount >= 100
 order by (aa.AcceptedAnswersCount * 100.0) / ga.PostedAnswersCount desc
)
select *
  from SpecificUser
union all
select *
  from TopUsers;

fork query

edited jun 2

created jun 2 jachguate