Acceptable quality of SO questions with answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Gardening and Landscaping

Q&A for gardeners and landscapers

-- 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
p.acceptedanswerid is not null
and p.score > 100
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 (
        LOWER(p.Title) LIKE '%what%'
        OR LOWER(p.Title) LIKE '%why%'
        OR LOWER(p.Title) LIKE '%where%'
        OR LOWER(p.Title) LIKE '%how%'
        OR LOWER(p.Title) LIKE '%when%'
        OR LOWER(p.Title) LIKE '%which%'
        OR LOWER(p.Title) LIKE '%who%'
        OR LOWER(p.Title) LIKE '%do%'
        OR LOWER(p.Title) LIKE '%does%'
        OR LOWER(p.Title) LIKE '%could%'
        OR LOWER(p.Title) LIKE '%should%'
        OR LOWER(p.Title) LIKE '%would%'
        OR LOWER(p.Title) LIKE '%will%'
        OR LOWER(p.Title) LIKE '%did%'
        OR LOWER(p.Title) LIKE '%might%'
        OR LOWER(p.Title) LIKE '%must%'
)
and (p.tags LIKE '%python%' OR p.tags LIKE '%pandas%')

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results