Data Export for the research project of http://twitter.com/se_statistics
Q&A about the site for theoretical computer scientists and researchers in related fields
SELECT p.Id as antwort_id, q.Id as frage_id, p.Body, p.Score, p.OwnerUserId, u.Reputation, q.AnswerCount, q.ViewCount, p.CreationDate , p.Id as [Post Link] FROM Posts as p JOIN Posts as q on p.Id = (select top 1 Id from posts where parentId = q.Id and CreationDate < '2011-09-01 00:00:00' and CreationDate >= '2010-09-01 00:00:00' -- from Year 2010/2011 and LastEditDate < DATEADD(month, -3, GETDATE()) -- at least 3 months untouched and LEN(Body) > 450 -- more than 450 chars used (TODO: striptags) ORDER BY NEWID()) -- only answers JOIN Users as u on p.OwnerUserId = u.Id where 1 = 1 and q.ClosedDate IS NULL and p.CommunityOwnedDate IS NULL -- not closed, not community wiki and q.CreationDate < '2011-09-01 00:00:00' and q.CreationDate >= '2010-09-01 00:00:00' -- from Year 2010 and q.AnswerCount <= 10 -- not more than 30 answers (pagination) and q.Score >= 0 -- No Question that got bad votes and (SELECT count(*) FROM Votes as v WHERE p.Id = v.PostId AND v.VoteTypeId = 3 ) = 0 -- Exclude posts with downvotes ORDER BY NEWID()