Custom query - Average answers per user question

Question

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.

Answer 1

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.