Get average post score of TOP 10 tags on StackOverflow using Stack Exchange Data Explorer

Question

I want to use the Stack exchange data explorer in order to display some basic information about the top tags.

The query should print the number of questions and answers, the average number of answers per question and the average score of posts(questions and answers under such questions). The query is ordered by the total number of questions.

This is my SQL code:

SELECT TOP(10)
  Tags.TagName,
  COUNT(DISTINCT Questions.Id) AS 'Number of Questions',
  COUNT(Answers.Id) AS 'Number of Answers',
  (COUNT(Answers.Id)+0.0)/COUNT(DISTINCT Questions.Id) AS 'Answers per question',
  (
    SELECT AVG(post.Score+0.0) 
    FROM Posts post,PostTags tag
    WHERE 
      (
      post.Id=tag.PostId 
      OR post.ParentId=tag.PostId
      )
      AND tag.TagId=Tags.Id
      ) AS 'average score of posts under this tag(questions and answers)'
FROM Posts Questions
LEFT JOIN Posts Answers
  ON Answers.ParentId = Questions.Id
INNER JOIN PostTags
  ON Questions.Id=PostTags.PostId
INNER JOIN Tags
  ON PostTags.TagId=Tags.Id
GROUP BY Tags.Id,Tags.TagName
ORDER BY COUNT(*) DESC

You can look at the script in the data explorer here.

The problem is that the script times out(in around 100 seconds). If I remove the OR post.ParentId=tag.PostId, it still takes around 25 seconds to run (for Stack Overflow) but it does not time out.

If I run my script on Super User, it does not time out. (It runs around 4 seconds, I think it is because Super User is smaller than Stack Overflow)

Is there a way to run optimize the script so that it does not time out on Stack Overflow without losing functionality and why does that simple OR decrease the performance that much?

The OR is that answers do also count for the average score.

I've added the execution plan files here.

Answer 1

Oh well, I didnt notice the sql-server tag...

What a detailed execution plan they have :)

But anyway the answer may be as simple as that there is more answers than there are questions.

And because you are making another subselect for the column and you are selecting what you already have in the outer select, this may get expensive quite fast.

So I would move the subquery from columns to source tables.

Something like this (sorry, I'm not familiar with sql server syntax):

SELECT TOP(10)
  Tags.Id,
  Tags.TagName,
  COUNT(Questions.Id) AS 'numOfQuestions',
  SUM(Answers.AnswerCount) AS 'numOfAnswers',
  (SUM(Answers.AnswerCount) + 0.0) / COUNT(Questions.Id) AS 'answersPerQuestion',
  (SUM(Questions.Score) + SUM(Answers.ScoreSum) + 0.0) / (COUNT(Questions.Id) + SUM(Answers.AnswerCount)) AS 'averageScore'
FROM Tags
INNER JOIN PostTags
  ON PostTags.TagId = Tags.Id
INNER JOIN Posts Questions
  ON Questions.Id = PostTags.PostId
LEFT JOIN (
  SELECT
    Question.Id AS QuestionId,
    COUNT(Answer.Id) AS AnswerCount,
    Sum(Answer.Score) AS ScoreSum
  FROM Posts Question
  INNER JOIN Posts Answer
    ON Question.Id = Answer.ParentId
  GROUP BY Question.Id
) AS Answers
  ON Answers.QuestionId = Questions.Id
GROUP BY Tags.Id, Tags.TagName
ORDER BY COUNT(Questions.Id) + SUM(Answers.AnswerCount) DESC

Few notes:

I'm intentionally doing left join with the subquery and then inner join in the subquery itself to limit the size of the subquery result. But this means that the outer query must handle possible nulls and replace them with zeroes, which I have not done (because idk sql server syntax), but you should know what I mean.

There is a lot of aggregations going on, but only top 10 are selected in the end. It is possible that the above approach may still not be the most performant. In which case I would recommend to split the query into few separate queries. First extract top 10 using only what is needed for this decision. Then aggregate the other stuff on subset of all posts limited to those 10 tags.

EDIT: I have now noticed that in your question you state:

The query is ordered by the total number of questions

But your query is not doing that. It orders by COUNT(*) which means that a question without answers is counted as +1 and question with N (N>0) answers is counted as +N.

At first I made my query sort it by number of questions and answers together. Which is yet something different. But to comply with your statement, it should be ORDER BY COUNT(Questions.Id) DESC only. And should have been COUNT(DISTINCT Questions.Id) in your original query...