count of answers after specific edit type until another edit

1

Please login or register to vote for this query.

(click on this box to dismiss)

Linguistics Meta

Q&A about the site for professional linguists and others with an interest in linguistic research and theory

select * from (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 /* body edits */
 ) 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%example%'
        and lower(phis.Comment) not like '%solution%'
        and lower(phis.Comment) not like '%attempt%'
     and lower(phis.Comment) not like '%code%'  
     and lower(phis.Comment) not like '%clarify%'
     and lower(phis.Comment) not like '%format%'
     and lower(phis.Comment) not like '%title%'
     and lower(phis.Comment) not like '%tag%'
 ) as pright /* body edits containing "add example" on desc */
on pleft.PostId = pright.PostId
where pleft.CreationDate > pright.CreationDate) t
/* count of body edits from one edit on body until another edit of body */
where t.rnq = 1) question
/*that count equals to 1*/

Enter Parameters

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