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;

FROM Posts
WHERE PostTypeId = 2 AND OwnerUserId = ##UserId:int##

but the second bit is giving me trouble.

  • What would this prove?
    – user273376
    Oct 18, 2014 at 10:41
  • 2
    @gone - Is "proving something" a necessary function of every query?
    – Richard
    Oct 18, 2014 at 10:52
  • of course not, was just curious
    – user273376
    Oct 18, 2014 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, 2014 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, 2014 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, 2014 at 11:03
  • That in itself is an intriguing pattern - maybe some wording riled people - are certain keywords downvote magnets?
    – user273376
    Oct 18, 2014 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, 2014 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, 2014 at 11:15

1 Answer 1


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

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, 2014 at 11:20

You must log in to answer this question.

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