0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientific skepticism

DECLARE @minQuestionCount int = 100
DECLARE @topN int = 20

SELECT TOP (@topN)
*
FROM (
  SELECT
    iif(Questions.TagName IN (
      'google-sheets',
      'google-apps-script',
      'formula',
      'google-sheets-query',
      'google-sheets-arrayformula'
      ),'google-sheets', Questions.TagName)
      AS TagName,
      Questions.Year AS Year,
      SUM(Questions.QuestionsCount) AS Questions,
      SUM(Unanswered.UnansweredCount) AS Unanswered,
      SUM(ZeroAnswers.ZeroAnswersCount) AS ZeroAnswers,
      SUM(Answers.AnswersCount) As Answers
  FROM
  (
  -- All Questions
  SELECT 
    count(PostId) AS QuestionsCount,  
    year(p.CreationDate) AS Year,
    TagName
  FROM PostsWithDeleted p, Tags t, PostTags pt
  WHERE p.PostTypeId = 1 AND p.id = PostId AND t.id = pt.TagId
  GROUP BY year(p.CreationDate), TagName
  ) as Questions
  INNER JOIN
  (
  -- Unanswered Questions
  SELECT 
    count(PostId) AS UnansweredCount, 
    year(p.CreationDate) AS Year, 
    TagName
  FROM PostsWithDeleted p, Tags t, PostTags pt
  WHERE p.PostTypeId = 1 AND p.id = PostId AND t.id = pt.TagId AND
    (p.ClosedDate IS NULL) AND
    NOT EXISTS
    (
      SELECT *
        FROM Posts a
        WHERE
          (a.ParentId = p.Id) AND
          ((a.Score > 0) OR (a.Id = p.AcceptedAnswerId))
    )
  GROUP BY year(p.CreationDate), TagName
  ) as Unanswered
  ON Questions.TagName = Unanswered.TagName AND
  Questions.Year = Unanswered.Year
  INNER JOIN
  (
  -- Zero-Answers Questions
  SELECT 
    count(PostId) AS ZeroAnswersCount, 
    year(p.CreationDate) AS Year, 
    TagName
  FROM PostsWithDeleted p, Tags t, PostTags pt
  WHERE p.PostTypeId = 1 AND p.id = PostId AND t.id = pt.TagId AND
    (p.ClosedDate IS NULL) AND
    NOT EXISTS
    (
      SELECT *
        FROM Posts a
        WHERE
          (a.ParentId = p.Id) AND
          ((a.Score >= 0) OR (a.Id = p.AcceptedAnswerId))
    )
  GROUP BY year(p.CreationDate), TagName
  ) as ZeroAnswers
  ON Questions.TagName = ZeroAnswers.TagName AND
  Questions.Year = ZeroAnswers.Year 
  INNER JOIN
  (
  SELECT 
    count(PostId) AS AnswersCount, 
    year(p.CreationDate) AS Year, 
    TagName
  FROM PostsWithDeleted p, Tags t, PostTags pt
  WHERE p.PostTypeId = 2 AND p.ParentId = PostId AND t.id = pt.TagId 
  GROUP BY year(p.CreationDate), TagName
  ) as Answers
  ON Questions.TagName = Answers.TagName AND
  Questions.Year = Answers.Year 
  GROUP BY iif(Questions.TagName IN (
      'google-sheets',
      'google-apps-script',
      'formula',
      'google-sheets-query',
      'google-sheets-arrayformula'
      ),'google-sheets', Questions.TagName), Questions.Year
) a
WHERE a.Questions >= @minQuestionCount AND
a.Year = ##Year##

ORDER BY a.Questions DESC
;

view current version

edited jan 30

created jan 30 Rubén