8

Please login or register to vote for this query.

(click on this box to dismiss)

Pull out the vital statistics of the key figures on your site.<br> 2 special columns: ValueAdd and Feedback<br> ValueAdd is the net-positive voting behaviour (upvotes less downvotes plus accepts given as well as any upvotes on answers to their questions - questions provide an opportunity for value to be added because answers can happen)<br> FeedbackRatio is the number of voting-opportunities taken over the number of voting occasions expected to be taken (expected to vote n all answers to your own questions, and all questions you answer, as well as an accept for each question you ask).<br> The rank is the order-by Feedback

Q&A about the site for expert genealogists and people interested in genealogy or family history

with Generated as (

    select ParentId as GenId, sum (Score) as Score
    from Posts
    where PostTypeId = 2
    group by ParentId

), Data as (

    select Users.Id as UserId,
           DisplayName,
           Reputation,
           Upvotes as GiveUp,
           DownVotes as GiveDown,
           sum (case when AcceptedAnswerId is null then 0 else 1 end) as Accepts,
           sum (case when PostTypeId = 1 then 1 else 0 end) as QuestionsAsked,
           sum (case when PostTypeId = 2 then 1 else 0 end) as AnswersGiven,
           sum (AnswerCount) as AnswerReplies,
           sum (case when VoteTypeId = 2 then 1 else 0 end) as GotUp,
           sum (case when VoteTypeId = 3 then 1 else 0 end) as GotDown,
           sum (IsNull(Generated.Score, 0)) as Generated
    from Users
         inner join Posts on Users.Id = Posts.OwnerUserId
         inner join Votes on Votes.PostId = Posts.Id
         left outer join Generated on Posts.Id = Generated.GenId
    where Votes.VoteTypeId in (2, 3)
      and Posts.PostTypeId in (1, 2)
      and Users.Reputation >= 150
    group by Users.Id,
             Users.DisplayName,
             Users.Reputation,
             Users.UpVotes,
             Users.DownVotes
    
    )

select UserId  as [User Link], DisplayName,
       Reputation, GiveUp, GotUp, GiveDown, GotDown, Generated,
       (GiveUp - GiveDown) + Generated as ValueAdd,
       Cast((GiveUp + GiveDown + Accepts) / (1.0 * AnswerReplies + AnswersGiven + QuestionsAsked) as Numeric(10,2)) as ContribRatio,
       AnswersGiven,
       AnswerReplies,
       QuestionsAsked,
       Accepts,
       Cast(case when QuestionsAsked = 0
          then 1.0
          else Accepts / Cast(QuestionsAsked as NUMERIC(10,2))
       end as Numeric (10,3)) as AcceptRatio,
       Cast(case when IsNull(AnswerReplies, 0) = 0
          then 1.0
          else (GiveUp + GiveDown) / Cast(AnswerReplies as NUMERIC(10,2))
       end as Numeric(10,3)) as VoteRatio
from Data

view current version

edited jul 6 14

created jul 6 14 rolfl