0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for researchers, developers, and users of Tor

-- 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 3000
  --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 > 10
and p.creationdate > '01-JAN-2022'
and p.closeddate is null
and ans.score >=10
-- 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
and (
        p.title LIKE 'What%'
        OR p.title LIKE 'Why%'
        OR p.title LIKE 'Where%'
        OR p.title LIKE 'How%'
        OR p.title LIKE 'When%'
        OR p.title LIKE 'Which%'
        OR p.title LIKE 'Who%'
        OR p.title LIKE 'Can%'
        OR p.title LIKE 'Is%'
        OR p.title LIKE 'Are%'
        OR p.title LIKE 'Do%'
        OR p.title LIKE 'Does%'
        OR p.title LIKE 'Could%'
        OR p.title LIKE 'Should%'
        OR p.title LIKE 'Would%'
        OR p.title LIKE 'Will%'
        OR p.title LIKE 'Have%'
        OR p.title LIKE 'Has%'
        OR p.title LIKE 'Did%'
        OR p.title LIKE 'Was%'
        OR p.title LIKE 'Were%'
        OR p.title LIKE 'Am%'
        OR p.title LIKE 'Be%'
        OR p.title LIKE 'Might%'
        OR p.title LIKE 'Must%'
    )
and p.tags LIKE '%python%' OR p.tags LIKE '%pandas%'

fork query

edited mar 20 anonymous

created sep 3 20 David Marx