0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

select count(p.Id) as middle, question.PostId, question.Id, DATEDIFF(month,question.rightC, question.leftC) as time from  Posts p 
inner join 
(select Id, PostId, leftC, rightC from (select pright.Id, pleft.PostId, pleft.CreationDate as leftC, pright.CreationDate as rightC,
Row_number() OVER(PARTITION BY pright.Id order by pleft.CreationDate ASC) as  rnq
from 
(select phis.Id, phis.PostId, phis.CreationDate from PostHistory as phis 
 inner join Posts p on p.Id = phis.PostId 
 where p.PostTypeId = 1
 and phis.PostHistoryTypeId = 5
 ) as pleft
 inner join (
     select phis.Id, phis.PostId, phis.Comment, phis.CreationDate from PostHistory as phis 
   inner join Posts p on p.Id = phis.PostId 
     where p.PostTypeId = 1 
     and phis.PostHistoryTypeId = 5
       and lower(phis.Comment) like '%add%attempt%'
     and lower(phis.Comment) not like '%version%'
     and lower(phis.Comment) not like '%example%'
     and lower(phis.Comment) not like '%output%'
     and lower(phis.Comment) not like '%code%'
     and lower(phis.Comment) not like '%solution%'  
     and lower(phis.Comment) not like '%input%'
     and lower(phis.Comment) not like '%format%'
     and lower(phis.Comment) not like '%title%'
     and lower(phis.Comment) not like '%tag%'
 ) as pright
on pleft.PostId = pright.PostId
/* time between this edition and the edtion before this edition*/
where pleft.CreationDate > pright.CreationDate) t
where t.rnq = 1) question
on question.PostId = p.ParentId
and (p.CreationDate > question.rightC and p.CreationDate < question.leftC)

group by question.Id, question.rightC, question.leftC, question.PostId

fork query

edited jun 8 19

created jun 7 19 Hai.Li