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).
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 DESCthree 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?