Can we get the number of questions which were closed (include deleted ones too) and had some answers?
And more specifically I need the questions which were closed for lack of context. Does the sede allow filtering via "closed reason"
This will help us figure out the number of poor (low quality) questions being answered and then we can check if such a phenomenon decreased (or not as a result of EoQS)
The above is not urgent but we need to get some data on this
Stack Exchange releases "data dumps" of all its publicly available content roughly every three months via archive.org, and also makes that information queryable over the Internet at the Stack Exchange Data Explorer (SEDE) (updated weekly, on Sunday). Over time, as new features and other bits of d...
@ParamanandSingh This depends a bit on whether you plan to run this yourself or whether you plan to ask a CM. The CMs have access to an internal SEDE instance which has all data also for deleted posts: How much did "advice for first-time askers" help?
Using AnswerCount>0 you would get the questions which have at least one answer. But that doesn't work for deleted posts.
For deleted posts you would need to check for answers in some other way. But it is still possible also using the public instance of SEDE. Here is an example of query that counts answers for deleted questions: data.stackexchange.com/math/query/845040/…
I think you should be able to get the questions closed as off-topic from the PostHistory tabke,
> If PostHistoryTypeId = 10, this field contains the CloseReasonId of the close reason (listed in CloseReasonTypes): Old close reasons: 1 = Exact Duplicate 2 = Off-topic 3 = Subjective and argumentative 4 = Not a real question 7 = Too localized 10 = General reference 20 = Noise or pointless (Meta sites only) Current close reasons: 101 = Duplicate 102 = Off-topic 103 = Unclear what you're asking 104 = Too broad 105 = Primarily opinion-based
But again, this would only work for the existing posts, not the deleted ones. (Unless it is run on the internal instance.)
The documentation explicitly says about the PostHistory table: "Note that the history of deleted posts is scrubbed from this table in SEDE."
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. — Paramanand Singh ♦4 hours ago
@ParamanandSingh Well, the only difference is that the CMs can run queries also for deleted questions in the same way we can for the existing ones.
But - to name just one specific example - also we can get number of closed questions with answer including the deleted ones - it'll be just a bit more complicated than just adding the condition AnswerCount>0
Let me try to do this - that should not be too difficult.
This is just a test - to see whether the two methods (AnswerCount and CTE) will give the same result if I look at the Posts table: data.stackexchange.com/math/query/1560973/…
@ParamanandSingh The above query would be one of the things you asked about - the questions which are closed and have at least one answer.
As I said, I do not think there is a possibility to have only the questions closed for lack of context there.
I think it is possible to get questions closed as off-topic (which includes, among other things, lack of context) - but only for existing posts, not the deleted ones. (But the CMs would be able to run the same query on the database that includes deleted posts.)
To include a specific example, in this query I looked at the duplicated closures and I used: (ph.PostHistoryTypeId=10) AND (ph.Comment IN (1,101)).
@ParamanandSingh Probably I should also point out that there is a SEDE chatroom - and you're more likely to get a good advice concerning SQL there. (And I am pretty sure that at least some of the mods are more fluent in SQL than I am.)
But maybe we should discuss here first - after all we have already started. And if we need some additional advice, we might ask in the SEDE room.
Looking at the time in your and my timezone, this is seems like a daytime in both places.
But I'll have to go to do some stuff IRL. (I have some teaching to do later today.)
In any case, I think we probably went through the things that I was able to answer immediately.
