Average answer score by the Log2 reputation of the answerer *at the time of the posting*

1

Please login or register to vote for this query.

(click on this box to dismiss)

This query groups the answers by the Log2 reputation of the answerer *at the time of posting the answer*. The reputation is not exact (counts the total answer score * 10), but nearly passes the truth. The horizontal line shows the Log2 Reputation! Thus, for example, 17 means reputation between 131072 and 262144!

Amateur Radio

Q&A for amateur radio enthusiasts

DECLARE @AnswerNo INT;
SELECT @AnswerNo = COUNT(*)
FROM Posts
WHERE PostTypeId = 2;

WITH PostRep AS (
  SELECT
    Posts.Id,
    MIN(Posts.CreationDate) AS CreationDate,
    MIN(Posts.OwnerUserId) AS OwnerUserId,
    0.1 * SUM(CASE WHEN VoteTypeId = 1 THEN 15
      WHEN VoteTypeId = 2 THEN 10
      ELSE -2 END) AS Score
  FROM Posts, Votes
  WHERE
    Posts.Id = Votes.PostId
    AND Votes.VoteTypeId IN (1, 2 , 3)
    AND Posts.PostTypeId = 2
  GROUP BY Posts.Id
),
Raw AS (
  SELECT
    PostRep.Score AS Score,
    SUM(PostRep.Score) OVER (
        PARTITION BY Users.Id
        ORDER BY PostRep.CreationDate
        ROWS UNBOUNDED PRECEDING
    ) AS RunningScore
  FROM PostRep, Users
  WHERE PostRep.OwnerUserId = Users.Id
),
LogGroupedRaw AS (
  SELECT
    CAST(LOG(RunningScore * 10)/LOG(2) AS INT)
      AS LogReputation,
    Score
  FROM Raw
  WHERE RunningScore > 0
)
SELECT
  LogReputation,
  AVG(Score) AS AvgScore,
  100.0 * COUNT(*) / @AnswerNo AS PctAnswers
FROM LogGroupedRaw
GROUP BY LogReputation
ORDER BY LogReputation

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results