Shows the answer distribution, their average score and the required content length for upvote, grouped by their length logarithmically in 20% increasing groups
Q&A about the site for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing
DECLARE @AnswerNo INT; SELECT @AnswerNo = COUNT(*) FROM Posts WHERE PostTypeId = 2; WITH Raw AS ( SELECT CAST(LOG(LEN(Body))/LOG(##LogBase:FLOAT?1.2##) AS INT) AS LogLength, Score FROM Posts WHERE PostTypeId=2 ), LogGrouped AS ( SELECT CAST(POWER(##LogBase##, LogLength) AS INT) AS Length, AVG(1.0 * Score) AS AverageScore, COUNT(*) AS NumOfAnswers FROM Raw GROUP BY LogLength ), LogGroupedRaw AS ( SELECT Length, AverageScore, 100.0 * NumOfAnswers / @AnswerNo AS PctOfAnswers FROM LogGrouped ) SELECT *, 1000.0 * AverageScore / Length AS UpvotePerKilobyte FROM LogGroupedRaw WHERE PctOfAnswers > ##MinimalPercent:FLOAT?0.2## ORDER BY Length