10

I have downloaded the data dump which has several files like posts.xml, posthistory.xml, although I cannot find the compete version of the closed questions: for example, its title, owner user id, comments, answers.

Can anyone help me to find all entire closed questions set?

  • Have you taken a look at Data Explorer? It seems to be a MS SQL Server database + query tools based on TSQL procedures. – hardmath Mar 28 '13 at 23:32
  • @hardmath I am new here. So I cannot understand what your comment means. Could you explain it with more details ? Thanks – Qiongjie Mar 28 '13 at 23:36
  • This should help you get started: data.stackexchange.com/stackoverflow/query/105395/… – Richard J. Ross III Mar 28 '13 at 23:43
  • @Qiongjie Here is the link to Data StackExchange. – Taryn Mar 28 '13 at 23:43
  • It's the innocuous looking data link toward the bottom of the page. That page actually links to the query pages for a variety of StackExchange sites. – hardmath Mar 28 '13 at 23:43
  • But I do not know how to compose a query. could you write one for me ? I want to get all the closed questions – Qiongjie Mar 29 '13 at 0:00
  • Sure, I'll give it a try. There's actually a way to share queries that members compose. – hardmath Mar 29 '13 at 22:37
4

As with many programming problems, the easy way to do things is to crib from someone who already did it.

Caveat: The following is tied to the Mathematics StackExchange site, basically because it's a good size but not gargantuan like StackOverflow. Each SE site has its own query interface, so you'll need to pick one to run a query against (rather than all sites at once).


You'll probably want to create a login account for the StackExchange Data Explorer. By design you can preserve some extra anonymity by creating an account name there which is unrelated to other site activities, though I did not feel motivated to do so.

I looked through search results for queries containing "close" and found this one:

obtain all closed questions

SELECT *
FROM POSTS
WHERE CLOSEDDATE IS NOT NULL

Probably you really intend to do something more refined, perhaps in the way of a range of dates posted (or closed). Post a follow-up comment if you'd like a bit more of a tutorial. The link in the caption above let's you Run Query, and there are a couple of optional settings to play with.

  • I have tried it. But I can only retrieve 3557 posts. However, the number of closed questions should be larger. – Qiongjie Mar 29 '13 at 23:20
  • Forking the query, so I could edit it, and replacing SELECT * with SELECT COUNT(*) we find that there have been roughly 3,557 posts closed on the Math.SE forum. This probably doesn't include deleted posts. But it says about 3% of math Questions there have been closed for one reason or another. – hardmath Mar 29 '13 at 23:21
  • @Qiongjie: Perhaps you want to query the StackOverflow site instead? See my caveat in the Answer. – hardmath Mar 29 '13 at 23:23
  • you mean change the query to be: SELECT * FROM STACKOVERFLOW WHERE CLOSEDDATE IS NOT NULL right ? – Qiongjie Mar 29 '13 at 23:25
  • @Qiongjie: No, that would not work. Go back to the main Data Explorer page I linked to in the Comments on your Question (as did others). From there you can pick the StackOverflow Data Explorer (it's the biggest one, at the top of the list, probably larger than all the others combined). The word POSTS in the SQL Query refers to a table, and each Data Explorer has a separate schema for each StackExchange site, so the one for Mathematics has only records pertaining to that site. – hardmath Mar 29 '13 at 23:29
  • I tried that before. I can get 50000 posts. Does it mean there are only 50000 closed posts ? – Qiongjie Mar 29 '13 at 23:35
  • 1
    @Qiongjie: It does look like a suspiciously round number. Have you tried the SELECT COUNT(*) FROM POSTS WHERE CLOSEDDATE IS NOT NULL version there? Bear in mind that StackOverflow has well over 4 million posts (Questions), so I too would expect a larger number of closed posts than 50,000. Probably that's a limit set on query execution through the Data Explorer. – hardmath Mar 29 '13 at 23:40
  • 1
    related query I've just created: data.stackexchange.com/stackoverflow/query/291975/… – cregox Mar 23 '15 at 20:08

You must log in to answer this question.

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