How to make this query better (e.g. avoid repeating subquery in a query)?

Question

Stack Exchange provides access to query their MS SQL databases. They have a table Posts where each post is either a question or an answer (see the schema on right hand side here).

I saw a query for questions answered by more than three 50k users:

SELECT DISTINCT Q.Id AS [Post Link], Q.AnswerCount
FROM Posts Q
JOIN Posts A1 on A1.ParentId = Q.Id 
  AND A1.OwnerUserId IN (SELECT TOP ##TopN## Id FROM Users ORDER BY Reputation DESC)
JOIN Posts A2 on A2.ParentId = Q.Id
  AND A2.OwnerUserId IN (SELECT TOP ##TopN## Id FROM Users ORDER BY Reputation DESC)
  AND A1.Id != A2.Id
JOIN Posts A3 on A3.ParentId = Q.Id
  AND A3.OwnerUserId IN (SELECT TOP ##TopN## Id FROM Users ORDER BY Reputation DESC)
  AND A3.Id != A1.Id AND A3.Id != A2.Id
WHERE Q.AnswerCount = 3

I was wondering how to improve the query (balance between efficiency and readability)? For example:

• Is it possible to avoid repeating the subquery SELECT TOP ##TopN## Id FROM Users ORDER BY Reputation DESC three times, and instead just have it once?

• Will it be more efficient if switching the order of filtering out questions with less than three answers and checking if a question is answered by more than three 50k users?

Answer 1

The query title is

Questions answered by more than 3 50k users

But that doesn't describe what the query actually does. The query selects questions with three answers given by three distinct top n rated users. They happen to have more than 50k rep on that site, but that's a mere coincidence.

That can also be done by this query, containing only 1 subquery to get the top rated users:

SELECT Q.Id AS [Post Link], Q.AnswerCount
FROM Posts Q
WHERE Q.AnswerCount = 3
AND
(
  SELECT COUNT( DISTINCT A.OwnerUserId)
  FROM Posts A
  WHERE A.ParentId = Q.Id
  AND A.OwnerUserId IN
  (
    SELECT TOP ##TopN## Id
    FROM Users
    ORDER BY Reputation DESC
  )
) = 3

For the record, the query doing what the title says would be

SELECT Q.Id AS [Post Link], Q.AnswerCount
FROM Posts Q
WHERE Q.AnswerCount >= 3
AND
(
  SELECT COUNT(DISTINCT A.OwnerUserId)
  FROM Posts A
  JOIN Users U ON A.OwnerUserId = U.Id
  WHERE A.ParentId = Q.Id
  AND U.Reputation >= 50000
) = Q.AnswerCount

Assuming that the intention was to get questions with answers from 50k users exclusively.