How the question ratio of the lower and higher-rep users changed with the years

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH Raw AS (
  SELECT
    FLOOR(LOG(Users.Reputation)/LOG(##LogBase:float?5##)) 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
  DATEFROMPARTS([Year], 1, 1) AS [Year],
  'Rep between ' + CAST(FLOOR(POWER(##LogBase##, LogRep)) AS VARCHAR(MAX))
    + '-' + CAST(FLOOR(POWER(##LogBase##, LogRep+1)-1) AS VARCHAR(MAX)) AS Rep,
  100.0 * COUNT(CASE WHEN PostTypeId = 1 THEN 1 ELSE NULL END)
    / COUNT(*) AS QuestionPct
FROM Raw
GROUP BY Raw.[Year], Raw.LogRep
ORDER BY Raw.[Year] DESC, Raw.LogRep

Enter Parameters

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