Question ratio by int(log10(reputation/10))

1

Please login or register to vote for this query.

(click on this box to dismiss)

"x" is the rounded-down log10 of the hundredth reputation of the users: x=0 means rep < 100. x=1 means 100 <= rep < 1000. x=2 means 1000 <= rep < 10000. And so on. On "y", we can see the percentage of the questions in their all posts. It counts with the current reputation, not at the time of the post.

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Raw AS (
  SELECT
    CASE
      WHEN Users.Reputation < 100 THEN 0
      ELSE FLOOR(LOG(0.1*Users.Reputation)/LOG(10))
    END AS LogRep,
    DATEPART(yyyy, Posts.CreationDate) AS [Year],
    Posts.PostTypeId
  FROM Posts, Users
  WHERE
    Posts.OwnerUserId = Users.Id
    AND
    Posts.PostTypeId IN (1, 2)
    AND
    Users.Id >= 1
)
SELECT
  LogRep,
  CAST([Year] AS VARCHAR(MAX)) AS [Year],
  100.0 * COUNT(CASE WHEN PostTypeId = 1 THEN 1 ELSE NULL END)
    / COUNT(*) AS QuestionPct
FROM Raw
GROUP BY LogRep, Raw.[Year]
ORDER BY Year, LogRep

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results