CODEWITHSUNDEEP
sqlt-sqldataexplorer
Maximillian Laumeister
Maximillian Laumeister

Reputation: 20359

SQL: How can I retrieve how many helpful answers a user has posted, using Stack Exchange Data Explorer?

I am trying to construct a SQL query for data.stackexchange.com that given a particular Stack Overflow user, will list their number of accepted answers, number of answers with score > 0, and number of answers that have either been accepted AND/OR have score > 0.

Here is the query I have so far, which only counts up accepted answers:

DECLARE @UserId int = ##UserId##

SELECT 
    Count(a.Id) AS Accepted
FROM
    Posts q
  INNER JOIN
    Posts a ON q.AcceptedAnswerId = a.Id
WHERE
    a.OwnerUserId = @UserId
  AND
    a.PostTypeId = 2

When you run it on my user id (2234742), it shows the following output:

Accepted
70

I am looking for output like the following:

Accepted     Score > 0    Accepted or Score > 0
X            X            X

To edit my existing query, you can go to my existing data.stackexchange.com query page and click "fork query" underneath the query body. The schema will be shown on the right. I am new to SQL, so thank you for the help!

N.B. Remember that Accepted or Score > 0 is not just the sum of the other two columns, because there is overlap!

Upvotes: 0

Views: 58

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT 
a.id,
sum(case when a.PostTypeId = 2 then 1 end) AS Accepted,
sum(case when a.PostTypeId = someval then 1 end) AS 'score>0',
sum(case when a.PostTypeId = someval then 1 end) AS 'acceptedorscore>0'
FROM Posts q INNER JOIN
Posts a ON q.AcceptedAnswerId = a.Id
WHERE a.OwnerUserId = @UserId
group by a.id, a.PostTypeId

Replace 'someval' with the posttype to get what you need. You need to group by posttype

Upvotes: 1

Related Questions