What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 131 Stack Exchange communities.

I'm a student trying to write a report for a class. Could anyone please help me write the SQL query to find the following data for Stack Overflow/Stack Exchange?

  • number of answers vs time, perhaps averaged across all stack overflow questions (or just across posts that have, say, more than 3 responses)

Is this possible? Basically I'm hoping to be able to produce a graph similar to the following one, taken from "Collective Wisdom: Information Growth in Wikis and Blogs" (Das and Magdon-Ismael, 2010).

screenshot

share|improve this question
    
Sorry, ignore my previous comment if you saw it (it's deleted now). I got this confused with another question I was looking at, and didn't realize you were asking about SEDE. However, I'm still not clear on exactly what kind of data you're asking for. –  Pops Dec 3 '11 at 18:31
    
Thank you so much kiamlaluno for prettifying my post T_T. And sorry about the murky explanation. I'm hoping to retrieve, for every question if possible, what time each answer/comment/edit was appended to the question (1 min. after the posting of the question, etc.). I'm hoping to create a graph where the reader can visualize, for time count after the question's birth on the x-axis, how many answers(/comments/edits) it received. –  user174118 Dec 3 '11 at 21:42
    
D-MI's paper (cited) models the dynamics of enormously successful media for collaborative information creation like Wikis and blogs, and Stack Exchange is the paragon of such a platform. So I was thinking SEDE posting dynamics would probably show similar trends as ones D-MI analyzed. I sadly don't know how to use SQL and my learning curve is really slow, but I got the inspiration for visualization from http://hewgill.com/~greg/stackoverflow/stack_overflow/stats.html. –  user174118 Dec 3 '11 at 21:46

1 Answer 1

up vote 1 down vote accepted

So a very gracious and talented friend of mine ended up writing this sql query for me, and told me to post it up here so I wanted to share it here:

SELECT SUM(1) as Comments, (datediff(minute, Question.CreationDate, Answers.CreationDate) / 30) as Delay
FROM Posts Question, Posts Answers
WHERE Question.Id = Answers.ParentId AND
Answers.PostTypeId = 2 AND
(SELECT COUNT(*)
FROM Posts TheseAnswers
WHERE TheseAnswers.ParentId = Question.Id
AND TheseAnswers.PostTypeId = 2) >= 3 AND 
datediff(minute, Question.CreationDate, Answers.CreationDate) < 1200 AND
datediff(minute, Question.CreationDate, Answers.CreationDate) >= 0
GROUP BY (datediff(minute, Question.CreationDate, Answers.CreationDate) / 30)
ORDER BY Delay​

The query retrieves, for questions that received 3 or more answers, the number of answers each question received every half hour.

The result is really beautiful. For those of you aware of power law distributions, the following is what the log-log plot of the data looks like.

On the x-axis is the log of time step and on the y-axis is the log of number of average answers received per half-hour...

Num answers per Q age

Thank you Stack Exchange for your wonderful platform and interface. =)

share|improve this answer

You must log in to answer this question.

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