2
$\begingroup$

Is there any way I can access the data of what fraction of questions are being closed as dupes per month. So, for example, I want to compare how many Qs posted in January this year was closed that month itself vs the January of last year.

Thanks.

$\endgroup$
5
  • $\begingroup$ You didn't understand my question. I am asking something more specific: How many questions posted in a month is closed in that month itself? @postmortes I want to know how the close rates are changing w.r.t time $\endgroup$ Feb 19, 2022 at 11:31
  • $\begingroup$ Just to clarify, to include an example, you want the questions that were both posted and closed in January? (For example, a question posted on January 20 and closed on January 27 qualifies, but the question posted on January 30 and closed on February 1 does not. The same for other months.) $\endgroup$ Feb 19, 2022 at 11:33
  • $\begingroup$ Right exactly @MartinSleziak $\endgroup$ Feb 19, 2022 at 11:34
  • $\begingroup$ To your first comment, which was just a tad rude (your question didn't make specifications; you only clarified in your comment, then later updated your post. The problem was not anyone failing to "understand" your question; it was you failed to specify in the first place, what you meant, in your original post. I'd really appreciate it if you remove your first comment above, Buraian. Please be kinder to those seeking clarification to posts that are not entirely clear. $\endgroup$
    – amWhy
    Feb 19, 2022 at 20:15
  • 3
    $\begingroup$ Hmm I actually never edited the contents of the question.. only the tags. $\endgroup$ Feb 19, 2022 at 21:12

1 Answer 1

12
$\begingroup$

For such inquiries, SEDE seems like a reasonable tool. I have tried to do some queries - I have no doubt that they can be modified and improved in various ways.

Description of the queries posted below

You might notice that - among other things - the queries return the number of questions which have been closed within 30 days. You can change this parameter - for example if you want to see how many questions were closed within 1 day or within 10 days etc.

Other parameters which can be changed are the dates - for example, you can look only at the questions posted since the beginning of 2021 to the end of January 2022, or some different date range.

The queries also include the percentage of questions which were closed - without any limits on the date of closure.

And also the questions which were closed in the same calendar month as they were posted. (For example, a question posted on January 20 and closed on January 27 qualifies, but the question posted on January 30 and closed on February 1 does not. The same for other months.) This was the main statistics required by the OP - but to me it seemed reasonable to included also other stats that might be relevant in this context.

In all these queries, the questions are grouped depending on the month when they were posted.

All closures

This is what I got: Percentage of questions closed within n days (This link shows the query with the default parameter n=30.)

Graph

However, this only returns the questions in the table Posts, i.e., the questions which haven't been deleted. Probably more interesting is to look at the data which include deleted questions, too - this can be achieved by using the PostsWithDeleted table.

Graph

Duplicate closures

I somehow missed that the question includes the requirement to look at the duplicate closures. To get the duplicates I could perhaps use the PostHistoryTable where close reasons (including duplicate) could be found. However, if I tried to identify the duplicate closures in this way, I would not be able to get the deleted posts. That's why I have tried to do this using the PostLinks table - to the best of my knowledge, that includes the deleted posts, too. (In fact, this was reported as a bug on Meta Stack Exchange. But for our purposes, it is useful that this table contains also the data about deleted posts - so I am not sure whether I would consider this a bug.)

If we're using the PostLinks table, it is important to include also the condition LinkTypeId=3 - so that we only count duplicates (and not all links). Originally, I did make this mistake - but I have updated both the queries and the included pictures since then.

This is what I get for the existing posts.

Graph

And here are the results which include the deleted posts.

Graph

These results should be taken with a grain of salt - considering my limited experience with SQL. (Additionally to that, the queries for the duplicate closures are probably a bit clumsy - I would not be surprised if there were a simpler way to do the same thing.) But I do think that this returns what the question asks for.

Some further remarks

  • As usually, it might be worth reminding that the data in SEDE is only updated once a week. (At the moment, the site says that the last update was on February 13. So for this month, we only have the data from the first half. We can reasonably expect that if the same queries are run at the end of the month or even later, the results for February will look differently.)
  • You can run the same query for other sites, if you want to compare the evolution of these stats here and elsewhere. (Just below the fields where you can enter parameters, you have also a field for choice of the site.)
  • I will also include a link to the Database schema documentation for the public data dump and SEDE - in case you want to find out more details on which data is available for deleted posts, too.
  • The closed posts are identified by checking whether ClosedDate is not NULL. This is the case for the questions which aren't closed now. If a question was closed and then reopened, it won't be included in the stats here.
  • In the queries about existing questions (rather then deleted questions) we see an increase in the recent months. This was expected - many of those question will probably be deleted at some point. But both manual deletions and roomba take some time - that's why we see less of them among the recent questions.
$\endgroup$
8
  • $\begingroup$ Could you give me the stats for the questions which are closed specifically as dupes? The EOQ has caused the statistics to become muddle a bit I think. I'm thinking that most of those closes are from low quality rather than dupe $\endgroup$ Feb 19, 2022 at 12:16
  • 2
    $\begingroup$ @Buraian Yes, I am working on it. (Only now I noticed you're asking about duplicates - I have somehow missed it.) Maybe you could add the tag (duplicates) tag to your question - since the question is about duplicates, it seems as a suitable tag. $\endgroup$ Feb 19, 2022 at 12:18
  • $\begingroup$ Also there is one more issue , these stats are absolute numbers if I understood and hence maybe inflated due to more questions being asked. Could fraction of questions closed be given? $\endgroup$ Feb 19, 2022 at 12:24
  • 1
    $\begingroup$ @Buraian This gives percentage of all question that have been closed (or closed as duplicates), not absolute numbers. (In the queries, I am dividing the numbers by countall - which should be number of questions posted in the given month.) If we need to discuss the queries further, feel free to ping me in chat.) $\endgroup$ Feb 19, 2022 at 12:53
  • $\begingroup$ I need such queries with some further conditions in order to get some idea of effect of EoQS. Will discuss in chat in a day or two. $\endgroup$
    – Paramanand Singh Mod
    Feb 21, 2022 at 2:03
  • $\begingroup$ Could you give link to where you discussed @ParamanandSingh $\endgroup$ Feb 22, 2022 at 21:57
  • $\begingroup$ @Buraian: just check Martin Sleziak's room. $\endgroup$
    – Paramanand Singh Mod
    Feb 23, 2022 at 1:30
  • $\begingroup$ For future convenience, I link to the part of the transcript where Paramanand asked: chat.stackexchange.com/transcript/message/60478203#60478203 $\endgroup$ Feb 23, 2022 at 14:15

You must log in to answer this question.

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