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 ), 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