Statistics of fraction of questions closed per month (posted that month itself)

Question

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.

Answer 1

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.)

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.

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.

And here are the results which include the deleted posts.

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.