words per edit

1

Please login or register to vote for this query.

(click on this box to dismiss)

Emacs

Q&A for those using, extending or developing Emacs

;with 
WordsPerRevision as (
select 
       postid 
     , id
     , creationdate
     , lag(id, 1,null) over (partition by postid order by id) [prev]
     , count(*)  [words]
from posthistory 
cross apply string_split(text, ' ') 
where posthistorytypeid in (2, 5)
group by postid 
       , id
       , creationdate
),

Edits as (
select 
       ph.postid 
     , ph.id
     , ph.creationdate
     , this.words  [words]
     , wpr.words [prev words]
     , this.words - wpr.words [diff]
from posthistory ph
inner join WordsPerRevision this on this.id = ph.id
inner join WordsPerRevision wpr on wpr.prev = ph.id
where ph.posthistorytypeid = 5
and userid = ##userid?3804##
)

/*select top 100 *
from WordsPerRevision 
*/

select    postid [Post Link]
        , creationdate
        , datediff(mi, lag(creationdate) over (order by id) , creationdate) [minutes between edits]
        , words
        , [prev words]
        , [diff]
from edits
order by id

Enter Parameters

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