Average score per answer of users similar to userId (answer count between 80% and 120% of userId's answer count) compared to userId, everyone, and Jon Skeet
Q&A for scientists using computers to solve scientific problems
-- Average Accepted Answer Score: Jon Skeet, Everyone, Users Like Me, & Me -- Average score per answer of users similar to userId -- (answer count between 80% and 120% of userId's answer count) -- compared to userId, everyone, and Jon Skeet DECLARE @AnswerCount INT; DECLARE @MinimunAnswers INT; DECLARE @MaximumAnswers INT; DECLARE @Scores TABLE ( [Order] INT, [User] VARCHAR(50), Score DECIMAL(10,2) ); SELECT @AnswerCount = COUNT(*) FROM Posts WHERE Posts.PostTypeId = 2 --answers AND Posts.OwnerUserId = ##UserId## OPTION ( FORCE ORDER, LOOP JOIN ) SET @MinimunAnswers = @AnswerCount * .8 SET @MaximumAnswers = @AnswerCount * 1.2 INSERT INTO @Scores SELECT 1 AS [Order], 'Jon Skeet' AS [User], ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score] FROM Posts AS Answers INNER JOIN Posts AS Questions ON Answers.ParentId = Questions.Id WHERE Answers.PostTypeId = 2 -- answer AND Questions.AcceptedAnswerId = Answers.Id -- accepted answer AND Answers.OwnerUserId = 22656 -- Jon Skeeet OPTION ( FORCE ORDER, LOOP JOIN ) INSERT INTO @Scores SELECT 2 AS [Order], 'Everyone' AS [User], ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score] FROM Posts AS Answers INNER JOIN Posts AS Questions ON Answers.ParentId = Questions.Id WHERE Answers.PostTypeId = 2 -- answer AND Questions.AcceptedAnswerId = Answers.Id -- accepted answer OPTION ( FORCE ORDER, LOOP JOIN ) INSERT INTO @Scores SELECT 3 AS [Order], 'Everyone Like UserId: ' + CAST( ##UserId## AS VARCHAR ) AS [User], ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score] FROM ( SELECT Posts.Score, COUNT(*) AS AnswerCount FROM Posts WHERE Posts.PostTypeId = 2 --answer GROUP BY Posts.OwnerUserId, Posts.Score HAVING COUNT(*) BETWEEN @MinimunAnswers AND @MaximumAnswers ) AS Answers OPTION ( FORCE ORDER, LOOP JOIN ) INSERT INTO @Scores SELECT 4 AS [Order], 'UserId: ' + CAST( ##UserId## AS VARCHAR ) AS [User], ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score] FROM Posts AS Answers INNER JOIN Posts AS Questions ON Answers.ParentId = Questions.Id WHERE Answers.PostTypeId = 2 -- answer AND Questions.AcceptedAnswerId = Answers.Id -- accepted answer AND Answers.OwnerUserId = ##UserId## OPTION ( FORCE ORDER, LOOP JOIN ) SELECT * FROM @Scores ORDER BY [Order]