Heavily Duplicated 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

declare @duplicates table (relatedpostid int, tags nvarchar(250), ndupes int) 

insert @duplicates
select 
       --top 3
       --top 20
       top 100
       pl.relatedpostid, p.tags,
       count(distinct pl.postid)
from postlinks as pl
join posts as p
on pl.relatedpostid = p.id
where pl.linktypeid = 3
and pl.creationdate > '01-JAN-2020'
group by pl.relatedpostid, p.tags
having count(distinct pl.postid) > 3
order by count(distinct pl.postid) desc

/*
select pl.postid, 
       pl.postid as [Post Link],
       pl.relatedpostid as [Parent]
from postlinks as pl
join @duplicates as d
on pl.relatedpostid = d.relatedpostid
*/

select 
  --top 100
  top 30000
  --p.*,
  --ans.*,
  p.id as postid,
  pl.relatedpostid,
  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
left outer
join posts ans
on p.id = ans.parentid
and ans.posttypeid = 2
--left outer 
join postlinks pl
on p.id = pl.postid
--and pl.id is null
join @duplicates as d
on pl.relatedpostid = d.relatedpostid

where 1=1
-- stay consistent with the criteria we have on the random data
--.... err, maybe not.
--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

Enter Parameters

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