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