8
$\begingroup$

Is there a way with a query to calculate how the number of questions with accepted answers divided by asked questions has changed over time? One could, for example, find this as function of each month or week or something like that.

I am suspecting that this rate have gone down over the last year or so and I would be interested in seeing if this is true. (I would also be interested in knowing why this is true (if it is true)).

$\endgroup$
6
  • $\begingroup$ Hmm. I'm going to go poke at the Data Explorer and see what I can drudge up. I'm pretty sure that I could at least write a query that lets you specify the timeframe and spits out data. $\endgroup$
    – Dennis Meng
    Feb 11, 2014 at 5:53
  • $\begingroup$ On that note, I just learned that I can in fact make a graph on Data Explorer. There's hope yet! $\endgroup$
    – Dennis Meng
    Feb 11, 2014 at 6:05
  • $\begingroup$ In this answer you can find some user scripts, stackexchange API and some SEDE query related to accept rate. @DennisMeng $\endgroup$
    – Martin Sleziak
    Feb 11, 2014 at 6:37
  • $\begingroup$ Thanks. It's taking a bit since my SQL-foo is still not the greatest. But there's progress. $\endgroup$
    – Dennis Meng
    Feb 11, 2014 at 6:40
  • $\begingroup$ @Thomas I see that you have created a new tag (query). If you meant by this Data Explorer query, then there already exists a tag called (data-explorer). $\endgroup$
    – Martin Sleziak
    Feb 11, 2014 at 7:56
  • $\begingroup$ @MartinSleziak: I changed the tag. I do mean the Data Explorer query. Thanks. $\endgroup$
    – Thomas
    Feb 11, 2014 at 13:09

1 Answer 1

Reset to default
13
$\begingroup$

Okay, so I think I have the query down (not just close like last time I did a Data Explorer query)

Select 47 - Datediff(month, p.CreationDate, Current_Timestamp) as Age,
       convert(DECIMAL(10,2), sum(case when p.AcceptedAnswerId is null then 0
                                       else 1
                                  end)
               ) / count(p.Id) as PercentAccepted
From Posts p
Where p.PostTypeId = 1
Group by Datediff(month, p.CreationDate, Current_Timestamp)
Order by Datediff(month, p.CreationDate, Current_Timestamp) desc

should get it for you by month. The 47 - business was just adjusting the data so that the graphing wouldn't be in reverse (since older posts would have larger ages). Query can also be found here.

I'm assuming you wanted a graph, so if you go to the link and run the query, there'll be a "Graph" tab that'll let you see the results plotted. The graph is also reproduced below for convenience:

enter image description here

Judging from the graph, you're right. The percentage does seem to be dropping; it even dropped below 50%.

$\endgroup$
10
  • $\begingroup$ I might also clean up the query if people want; the skewing at the beginning appears to be from a solitary post that is much older than the others and doesn't have an accepted answer. $\endgroup$
    – Dennis Meng
    Feb 11, 2014 at 7:47
  • 1
    $\begingroup$ This is exactly what I wanted. Thank you for this! I appreciate the time you put into this. $\endgroup$
    – Thomas
    Feb 11, 2014 at 13:19
  • 1
    $\begingroup$ What does the data look like in comparison to Stack Overflow? $\endgroup$
    – Isaac
    Feb 11, 2014 at 14:07
  • $\begingroup$ @Isaac It so happens that I ran the same query on Stack Overflow on first by accident (as evidenced by this answer's revision history); the query itself is here with corresponding graph. Mathematics is doing better for now, but it's going in the same direction. $\endgroup$
    – Dennis Meng
    Feb 11, 2014 at 16:59
  • $\begingroup$ @DennisMeng: Great work, thanks. "... better for now, but it's going in the same direction" to me means that it's not necessarily a bad thing—if we're following in the path that's already been trodden by the order-of-magnitude-bigger Stack Overflow, we're probably doing okay. $\endgroup$
    – Isaac
    Feb 11, 2014 at 17:32
  • 1
    $\begingroup$ this is why i think having acceptance rate is not a bad idea. If you have someone who asked less than 5 question, just do not display a rate, but do so for people who asked more. $\endgroup$
    – Lost1
    Feb 12, 2014 at 2:36
  • $\begingroup$ @Lost1 The problem with that is that this notion of accept rate (and arguably the whole idea of accept rate) doesn't correlate to any measure of quality. Perhaps you tend to ask open questions; in many cases 'there's no known answer to this question' is an acceptable answer, but in some even strong answers don't actually answer the question, and in those cases it seems better not to accept so that others will be more inclined to look - but that's an entirely separate discussion. $\endgroup$
    – Steven Stadnicki
    Feb 13, 2014 at 18:00
  • $\begingroup$ Would it be possible to qualify this query by making the denominator not just all questions, but (say) all questions with positive votes that have an answer with positive votes (and of course to change the numerator as well, restricting the search to only such questions)? As it stands it's unclear to me whether this represents a decrease in people accepting good answers, or an increase in poor questions that never get answered in the first place (but never get closed), or some combination thereof. $\endgroup$
    – Steven Stadnicki
    Feb 13, 2014 at 18:02
  • $\begingroup$ @StevenStadnicki I think so. I'll give it a try later tonight. $\endgroup$
    – Dennis Meng
    Feb 14, 2014 at 1:46
  • $\begingroup$ @StevenStadnicki Slightly adjusted query here. Questions with score > 0 only, plus I ignored that one really old outlier question. Still dropping apparently. $\endgroup$
    – Dennis Meng
    Feb 17, 2014 at 3:04

You must log in to answer this question.

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