random dataset - acceptable quality of SO questions with answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- lets collect a set of random posts and questsions meeting the following criteria
-- * have accepted answers
-- * have positive score
-- * do not appear in the postlinks table
-- * ---> should I also constrain to tags from the duplicated posts? 
--        Nah. I can do that post hoc if I think I need to.

select 
  --top 100
  top 30000
  --p.*,
  --ans.*,
  p.id as postid,
  p.title,
  p.body,
  p.tags,
  p.acceptedanswerid,
  p.creationdate,
  p.score,
  p.viewcount,
  p.owneruserid,
  p.answercount,
  p.commentcount,
  p.favoritecount,
  -------------------
  ans.id as response_id,
  ans.body as response_body,
  ans.creationdate as response_creationdate,
  ans.score as response_score,
  ans.owneruserid as response_owneruserid,
  ans.commentcount as response_commentcount
  
from posts p
join posts ans
on p.id = ans.parentid
left outer join postlinks pl
on p.id = pl.postid
and pl.id is null
where 1=1
and p.acceptedanswerid is not null
and p.score > 1
and p.creationdate > '01-JAN-2020'
and p.closeddate is null
and ans.score >=0
-- this bit probably isn't necessary
and p.deletiondate is null
and ans.deletiondate is null
-- also probably not necessary
and p.posttypeid = 1
and ans.posttypeid = 2

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results