Duplicate questions finder

0

Please login or register to vote for this query.

(click on this box to dismiss)

Open Data

Q&A for developers and researchers interested in open data

select p.Title, p.Body, p.Id, p.CreationDate, JSON_VALUE(ph.Text, '$.OriginalQuestionIds[0]')
from posts p
inner join PostHistory ph on ph.postid = p.id
where p.posttypeid = 1  -- 1
and ph.PostHistoryTypeId = 10  -- closed
and ph.comment = 101  -- as duplicate
and p.CreationDate >= '2020-08-01';

/*-- CTE with year, week and start of week dates
with yw as (
select distinct 
    year(creationdate) as y 
 , datepart(week, creationdate) as w
 -- http://stackoverflow.com/a/3936891/578411
 , cast(
     DATEADD(
          day, 
          1 - DATEPART(dw, creationdate), 
          creationdate) 
      as date
    ) as sdow
from posts
)

-- grouping over the union
select sdow
 , sum(questions) as [questions]
 , sum(duplicate) as [duplicate]
from 
(
  -- questions from users < 25 rep
  select sdow
     , count(*) as [questions]
     , 0 as [duplicate]
  from posts p
  inner join users u on u.id = p.owneruserid
  right outer join yw 
   on yw.y = year(p.creationdate) 
  and yw.w = datepart(week, p.creationdate) 
  where p.posttypeid = 1 -- q 
  and u.reputation < 25 -- rep
  group by sdow
  union 
  -- duplicates on questions from users < 25 rep
  select sdow
     , 0
     , count(*)
  from posts p
  inner join users u on u.id = p.owneruserid
  inner join PostHistory ph on ph.postid = p.id 
  right outer join yw 
   on yw.y = year(ph.creationdate) 
  and yw.w = datepart(week, ph.creationdate) 
  where p.posttypeid = 1 -- q
  and u.reputation < 25 -- rep
  and ph.PostHistoryTypeId = 10 -- closed
  and ph.comment = 101 -- as duplicate
  group by sdow
) as   total
group by sdow
order by sdow*/

Enter Parameters

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