How to find questions closed as duplicates via the Data Explorer

Question

I am trying to gather information on

1. the number of questions marked as "duplicate" per week for a user with fewer than 25 reputation points from the math.stackexchange site
2. the average number of comments/answers per question for a user with fewer than 25 reputation points

For the first part, I have

-- 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 CloseReasonTypes crt 
right outer join yw 
 on yw.y = year(v.creationdate) 
and yw.w = datepart(week, v.creationdate) 
where p.posttypeid = 1 -- q
and u.reputation < 25 -- rep
and crt.closereasontypeid = 101
group by sdow
) as   total
group by sdow
order by sdow

Unfortunately, the second part for the duplicates on questions is not compiling and I suspect that the problem is from

inner join users u on u.id = p.owneruserid
inner join CloseReasonTypes crt 

I am trying to use a similar approach for the second part (average number of comments/answers) as well.

Is there a quick fix?

Answer 1

You need to join on the PostHistory table, that contains what's happened to posts. A PostHistory entry with a PostHistoryType = 10 means that the question has been closed and if the Comment on that PostHistoryEntry was 101 it was closed as a duplicate. E.g.

select *
from posts p
inner join PostHistory ph on ph.postid = p.id 
where p.posttypeid = 1 -- q
and ph.PostHistoryTypeId = 10 -- closed
and ph.comment = 101 -- as duplicate

So it sounds like you want something like this...

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