2
\$\begingroup\$

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?

\$\endgroup\$
4
  • \$\begingroup\$ Your title should state what your code does. Please read codereview.stackexchange.com/help/how-to-ask . \$\endgroup\$
    – BCdotWEB
    Jun 19, 2020 at 22:32
  • \$\begingroup\$ As the author of the SEDE query, (1) I'm happy that the (extensive!) version history of that query seems to be gone, and (2) because SQL class was 20 years ago, I started off with a simpler goal (static 50k) and eventually figured out how to incorporate the variable "top N". \$\endgroup\$ Jun 23, 2020 at 12:55
  • \$\begingroup\$ Re-viewing my own query, it's also apparent that I intended to find Q's with at least 3 answers by top-rated users, but have managed to restrict it to Q's with exactly 3 such answers. \$\endgroup\$ Jun 23, 2020 at 12:57
  • \$\begingroup\$ Hrmph; after incorporating Gert's improvements, the link to the SEDE query has also changed. data.stackexchange.com/unix/revision/1252021/1540600/… is the one for this question. \$\endgroup\$ Jun 23, 2020 at 13:04

1 Answer 1

2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.