7
\$\begingroup\$

A typical situation I feel happens from time to time is that a question got some response quickly, and the OP accepted one of these answers. Then a little later, maybe half a day, a new answer emerges, and doesn't get the upvotes it could have gotten if it had hit that limited window of popularity when the post is new or recently edited.

In order to address this, I tried writing an SEDE query to find answers with a low score from high reputation users (with the possibility to limit by tag). This is based on the assumption that those high reputation users most likely write good answers. If answers have a low score, they could be hidden gems.

Latest version of this SEDE-query.

DECLARE @reputationLimit AS INT = ##ReputationLimit:int?5000##;
DECLARE @lowScoreLimit AS INT = ##LowScoreLimit:int?5##;
DECLARE @tag as NVARCHAR(60) = ##Tag:string? ##;

SELECT TOP ##Limit:int?100##
  u.Id AS [User Link],
  a.Id AS [Post Link],
  a.CreationDate,
  a.Score,
  q.MaxAns,
  q.AvgAns,
  q.Tags
FROM Posts a
  JOIN Users u ON a.OwnerUserId = u.Id
  JOIN (
  SELECT q.Id, q.Tags, AVG(a.Score) AvgAns, MAX(a.Score) MaxAns
    FROM Posts q
    JOIN Posts a ON a.ParentId = q.Id
    WHERE q.AcceptedAnswerId IS NULL
    GROUP BY q.Id, q.Tags
  ) q
  ON a.ParentId = q.Id
WHERE (u.Reputation > @reputationLimit)
  AND a.Score <= @lowScoreLimit
  -- AND q.MaxAnswerScore = 0
  AND (@tag = '' or q.tags like '%'+@tag+'%')
ORDER BY a.CreationDate DESC

Is this a worthwhile approach and query? Do you think it is good trying to find such answers? Do you think the limits are somewhat reasonable?

\$\endgroup\$
  • 3
    \$\begingroup\$ You know, you could have the query peer reviewed on the main site ;-) \$\endgroup\$ – Mathieu Guindon Dec 12 '15 at 1:55
  • \$\begingroup\$ @Mat'sMug, True, but this is more a question on whether this a good idea and according to site policy, and not that much on the actual code review. \$\endgroup\$ – holroy Dec 12 '15 at 1:59
  • 1
    \$\begingroup\$ should be or q.tags like '%<'+@tag+'>%' ;-) \$\endgroup\$ – rolfl Dec 12 '15 at 5:04
  • \$\begingroup\$ @rolfl is the < > for word boundaries of something similar? \$\endgroup\$ – holroy Dec 12 '15 at 9:57
  • 1
    \$\begingroup\$ @holroy - no, it is embedded in the data in the column, so the data is something like <beginner><datetime><cobol> ... (see this temporary query) - Adding the <...> to the search means that queries for java get only java, and not all the javascript too. \$\endgroup\$ – rolfl Dec 12 '15 at 13:24
  • 2
    \$\begingroup\$ @rolfl MetaMetaMeta Review \$\endgroup\$ – RubberDuck Dec 17 '15 at 0:23
10
\$\begingroup\$

Such a query could be beneficial if done responsibly. My concern is that it might encourage ad hominem voting.

I suggest that you drop the u.Id column, and change a.Id to q.Id, so that you need to skim through all of the answers on the question before voting.

\$\endgroup\$
  • \$\begingroup\$ Has implemented this in the latest version of my script \$\endgroup\$ – holroy Dec 15 '15 at 15:17
0
\$\begingroup\$

Have updated the query somewhat, to reduce the focus on person which was not my intent. My intent was more related to locating good answers with low score, and one category of these answers I'm thinking is low score by high rep users.

In the updated query I therefore do as 200_success suggested and have removed the user information, and changed link to point to the question.

I've also limited the tag portion of the answer, so that one might write java and not get javascript. It is still possible to write java% or python% to get all tags starting with either prefix.

Updated query (http://data.stackexchange.com/codereview/query/407633/):

-- Limit: Max number of posts
-- ReputationLimit: Lower reputation limit
-- tag: Tag of question "Use % for wildcard in part of tag"
-- highestScore: Highest answer score
DECLARE @reputationLimit AS INT = ##ReputationLimit:int?5000##;
DECLARE @highestScore AS INT = ##highestScore:int?5##;
DECLARE @tag as NVARCHAR(60) = ##tag:string? ##;

SELECT TOP ##Limit:int?100##
  q.Id AS [Post Link],
  a.CreationDate,
  a.Score,
  q.MaxAns,
  q.AvgAns,
  q.Tags
FROM Posts a
  JOIN Users u ON a.OwnerUserId = u.Id
  JOIN (
  SELECT q.Id, q.Tags, AVG(a.Score) AvgAns, MAX(a.Score) MaxAns
    FROM Posts q
    JOIN Posts a ON a.ParentId = q.Id
    WHERE q.AcceptedAnswerId IS NULL
    GROUP BY q.Id, q.Tags
  ) q
  ON a.ParentId = q.Id
WHERE (u.Reputation > @reputationLimit)
  AND a.Score <= @highestScore
  -- AND q.MaxAnswerScore = 0
  AND (@tag = '' or q.tags like '%<'+@tag+'>%')
ORDER BY a.CreationDate DESC

I've also added hopefully more informative labels to the input parameters. Please do comment if you don't feel this is according to site policy, or encourages wrong behavior, and give suggestion as to correct for that.

\$\endgroup\$
  • 1
    \$\begingroup\$ Could you also include longer answers in this? Atleast on CR the longer answers seems more informative and indepth. \$\endgroup\$ – N3buchadnezzar Jun 27 '16 at 11:43
  • \$\begingroup\$ 1) how do tags work and 2) the current results contain duplicates. \$\endgroup\$ – Pimgd Jun 28 '16 at 8:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .