0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for amateur radio enthusiasts

-- Questions that might be red herrings:
-- "Error" in Title, High Pageviews, Many Answers, Each with upvotes
-- This looks for all questions tagged with <TAG> that have more than
-- X pageviews PER MONTH and sorts them by questions with the HIGHEST
-- average answer score. This means there are multiple different right answers.

-- TO FILTER BY TAG ADD CONDITION: AND Posts.Tags LIKE '%<npm>%' 

SELECT TOP 500 Posts.Id AS [Post Link], Posts.Title,
Posts.Tags,
MAX(Posts.ViewCount)/(DATEDIFF(Month, MIN(Posts.CreationDate), '2017-08-01')+1) as [Pageviews Per Month],
MAX(AcceptedAnswer.Score) as [Accepted Answer Score],
MAX(AllAnswers.Score) as [Best Answer Score],
COUNT(AllAnswers.Id) as [Total Answers Count],
AVG(AllAnswers.Score) as [Average Answer Score],
ROUND(STDEV(AllAnswers.Score), 0) as [Answer Score Std Dev]

FROM Posts

JOIN Posts as AcceptedAnswer ON Posts.AcceptedAnswerId = AcceptedAnswer.Id AND AcceptedAnswer.PostTypeId = 2
JOIN Posts as AllAnswers ON Posts.Id = AllAnswers.ParentId AND AllAnswers.PostTypeId = 2

GROUP BY Posts.Id, Posts.Title, Posts.Tags
HAVING LOWER(Posts.Title) LIKE '%error%' AND MIN(Posts.CreationDate) < '2017-01-01' AND COUNT(AllAnswers.Id)>1 AND MAX(Posts.ViewCount)/(DATEDIFF(Month, MIN(Posts.CreationDate), '2017-08-01')+1) > 2000 AND COUNT(AllAnswers.Id) > 3
ORDER BY [Answer Score Std Dev] DESC

fork query

edited aug 8 17

created mar 2 16 Andy.Hattemer