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.

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

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 1
    No. This is exactly what I wanted. Subtracting the original answers would remove my ability to construct a moving average.
    – Richard
    Commented 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 .