4

I'm trying to craft a DataExplorer query that shows:

1) How many questions a particular user has answered

2) The average number of other answers to those questions. (e.g. by other users)

The first seems simple enough;

SELECT COUNT(*)
FROM Posts
WHERE PostTypeId = 2 AND OwnerUserId = ##UserId:int##

but the second bit is giving me trouble.

  • What would this prove? – user273376 Oct 18 '14 at 10:41
  • 2
    @gone - Is "proving something" a necessary function of every query? – Richard Oct 18 '14 at 10:52
  • of course not, was just curious – user273376 Oct 18 '14 at 10:54
  • My original question showed some more info about my intention. My concern is that when a high-rep user answers a question, it actively discourages lower-rep users from answering. By looking at the average number of answers as a whole (1.6 per question) and comparing those to the average number of answers per question answered by high-rep users, I can see whether this is a real effect or merely my imagination. – Richard Oct 18 '14 at 10:56
  • 1
    Okay, yes, that does sound like an interesting query and it actually sounds like something that probably needs to be looked at. – user273376 Oct 18 '14 at 10:58
  • @Gone - Interestingly, when I put this info in the original post, it immediately attracted two downvotes. Removing it seems to have attracted two upvotes. – Richard Oct 18 '14 at 11:03
  • That in itself is an intriguing pattern - maybe some wording riled people - are certain keywords downvote magnets? – user273376 Oct 18 '14 at 11:05
  • 3
    Maybe it was the fact that there was simply extraneous information in the question. My edit has definitely streamlined what I was asking without losing any of the meaning – Richard Oct 18 '14 at 11:06
  • I've just applied this search to the top 10 users on Sci-fi SE. It looks like the average number of answers (where high-rep users have posted answers) is significantly higher than the average. 2.56 versus an average of 1.6 – Richard Oct 18 '14 at 11:15
5

There are a number of approaches, but the first one that jumped to me was to join the table against itself.

SELECT COUNT(DISTINCT A.Id) AS AnswerCount, COUNT(*) AS AnswersByAllUsers, COUNT(*) / COUNT(DISTINCT A.Id) AS Average
FROM Posts A
    INNER JOIN Posts B ON A.ParentId = B.ParentId
WHERE A.PostTypeId = 2
      AND B.PostTypeId = 2
      AND A.OwnerUserId = ##UserId:int##

I think that should get what you're looking for. Note, of course, that this represents the number and average answers per post the user answered, and not the count and average of other answers. So you can simply subtract to find the result of that, if that's what you're looking for, or alternatively you could add AND A.Id <> B.Id, but that could have unintended consequences in certain circumstances. Although I doubt they'd matter much.

  • 1
    No. This is exactly what I wanted. Subtracting the original answers would remove my ability to construct a moving average. – Richard Oct 18 '14 at 11:20

You must log in to answer this question.

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