« first day (2636 days earlier)      last day (872 days later) » 

4:49 AM
Hello @MartinSleziak
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
 
1 hour later…
6:05 AM
I will add a link to the documentation where you can see what is available: Database schema documentation for the public data dump and SEDE
354
Q: Database schema documentation for the public data dump and SEDE

Stu ThompsonStack 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/…
@ParamanandSingh I do not think this is possible. There is a feature request about this: Please add the individual close-as-off-topic reason in the Data Explorer. (We can upvote that question or add a bounty, but there is not guarantee that it will be implemented in some way.)
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."
To add some context for people who stumble upon this in chat, there is a related comment under this answer: Statistics of fraction of questions closed per month (posted that month itself).
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
 
2 hours later…
8:20 AM
Thanks a lot @MartinSleziak for all that info. I will check with CMs too to figure out what is available at their end.
@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
8:35 AM
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/…
And this is what I get if I uses PostsWithDeleted: data.stackexchange.com/math/query/1560974/…
@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)).
Yeah i get that. I will discuss this with CMs and refer them to messages in this chatroom if needed.
Using (ph.PostHistoryTypeId=10) AND (ph.Comment IN (2,102)) should give off-topic closures.
Thanks again for providing us help with this.
@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.
8:56 AM
Ok we will discuss later if needed as you have already provided a lot more information and I need to work on it and discuss with other mods too.

« first day (2636 days earlier)      last day (872 days later) »