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 enthusiasts and power users of Windows Phone OS

with Generated as (

    select ParentId as GenId, sum (Score) as Score
    from Posts
    where PostTypeId = 2
    group by ParentId
), VoteGen as (
    select PostId, 
           sum (case when VoteTypeId = 2 then 1 else 0 end) as GotUp,
           sum (case when VoteTypeId = 3 then 1 else 0 end) as GotDown
    from Votes
    where VoteTypeId in (2, 3)
    group by PostId
), 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 (IsNull(VoteGen.GotUp, 0)) as GotUp,
           sum (IsNull(VoteGen.GotDown, 0)) as GotDown,
           sum (IsNull(AnswerCount, 0)) as AnswerReplies,
           sum (IsNull(Generated.Score, 0)) as Generated
    from Posts
         inner join Users on Users.Id = Posts.OwnerUserId and Users.Reputation >= ##MinRep:int?500##
         left outer join VoteGen on VoteGen.PostId = Posts.Id
         left outer join Generated on Posts.Id = Generated.GenId
    where Posts.PostTypeId in (1, 2)
    group by Users.Id,
             Users.DisplayName,
             Users.Reputation,
             Users.UpVotes,
             Users.DownVotes
    
    )

select RANK ( ) OVER ( order by ((GiveUp + GiveDown + Accepts) / (1.0 * AnswersGiven + QuestionsAsked + AnswerReplies)) DESC ) as RankFeedBack,
       RANK ( ) OVER ( order by ((GiveUp - GiveDown) + Generated) DESC ) as RankValueAdd,
       UserId  as [User Link], DisplayName as Name,
       Reputation as Rep,
       
       -- Generated -> how much score has been spent as a result of your work
       -- things you vote on, plus votes on any answers to your questions
       --   ** double-counting here for answers on your questions??
       (GiveUp - GiveDown) + Generated as ValueAdd,
       Generated,
       
       -- How much feedback you gave as a proportion of your feedback expectations
       -- expected to vote on questions you answer
       -- expected to accept an answer to your questions
       -- expected to vote on answers to your questions
       Cast((GiveUp + GiveDown + Accepts) / (1.0 * AnswersGiven + QuestionsAsked + AnswerReplies) as Numeric(10,2)) as FeedbackRatio,

       Cast(case when QuestionsAsked = 0
          then 1.0
          else Accepts / Cast(QuestionsAsked as NUMERIC(10,2))
       end as Numeric (10,3)) as AccRat,

       Cast(case when QuestionsAsked = 0
          then 1.0
          else Generated / Cast(QuestionsAsked as NUMERIC(10,2))
       end as Numeric (10,3)) as GenRat,
       
       QuestionsAsked as [Q's],
       AnswersGiven as [A's],
       GiveUp as [V+Out],
       GiveDown as [v-Out],
       GotUp as [V+In],
       GotDown as [V-In],
       Accepts as [Acc]

       --,
       --AnswerReplies,
       --Accepts
from Data
order by FeedbackRatio desc

fork query

edited feb 11 15

created jul 6 14 rolfl