4

For example, there are N questions which have a tag named X (there might be other tags also) and M of the questions remain unanswered. I require the number M associated with all tags in Stack Overflow.

  • 1
    There are thousands of tags on Stack Overflow. You need the number M for all of them? Is this for some data analysis stuff? – Blue May 18 at 2:25
  • @Blue Yes, I need the number for all of them. It is for an empirical study purpose. – Saikat Mondal May 18 at 2:31
  • @SaikatMondal You'll need to clarify the question a bit more. Say, do you need the "live" values, or will 1-week-out-of-date values also suffice? – Blue May 18 at 2:33
  • @Blue live value is preferable but values of 1-week-out-of-date are also okay. Basically, I am asking the SEDE query. I tried but it exceeds the time limit with no records. – Saikat Mondal May 18 at 2:36
  • @Blue I also tried top N, but did not find get the result. So, I thought that my query might not efficient (or wrong). Could you please try for me once? – Saikat Mondal May 18 at 2:42
  • This will count all questions with no answers for a given tag data.stackexchange.com/outdoors/query/1053037/… – Charlie Brumbaugh May 18 at 2:48
  • @Charlie Brumbaugh when I give java as Tag name it returns 0. Moreover, it is not feasible to provide such a high number of tags one by one. – Saikat Mondal May 18 at 3:04
  • Possibly useful: Top voted unanswered question and the associated tags. That is a start, perhaps someone more skilled in SQL will improve that for you. As is it sorts the highest voted unanswered questions and shows all the tags for each question. You could change it to sort on single tags but then there would be duplicate results. – Rob May 18 at 4:08
3

Here you go:

enter image description here

SELECT t.TagName, COUNT(DISTINCT(p.Id))
  FROM Tags AS t
  INNER JOIN PostTags AS pt ON t.Id = pt.TagId
  INNER JOIN Posts AS p ON p.Id = pt.PostId
  WHERE p.AnswerCount = 0
  GROUP BY t.TagName
  ORDER BY COUNT(DISTINCT(p.Id)) DESC

Feel free to fork the query to experiment. You might be interested in the awesome tutorial as well.

  • 1
    @CharlieBrumbaugh answers don't appear in the PostTags table, so filtering on post type isn't necessary. – Glorfindel May 20 at 6:55
  • it’s always run faster for me when I added that information to the join – Charlie Brumbaugh May 20 at 12:41
  • I've done some testing, and for me it depends on which version of the query I try first, that's always the slowest one. – Glorfindel May 20 at 12:42

You must log in to answer this question.

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