0

Please login or register to vote for this query.

(click on this box to dismiss)

https://meta.stackexchange.com/a/300181/158100 well, not only questions ... all posts

Q&A for anime and manga fans

with spoil(id, postid, length, start, eol) as
(
select id
     , ph.postid
     , len(text) length
     , IIF(charindex('>!', text, 1) > charindex('> !', text, 1)
       , charindex('>!', text, 1)
       , charindex('> !', text, 1)
       ) start
     , IIF( 
         charindex(char(13) + char(10)
         , text
         , IIF(charindex('>!', text, 1) > charindex('> !', text, 1)
           , charindex('>!', text, 1)
           , charindex('> !', text, 1)
           )
         ) = 0
       , len(text)
       , charindex(char(13) + char(10)
         , text
         , IIF(charindex('>!', text, 1) > charindex('> !', text, 1)
           , charindex('>!', text, 1)
           , charindex('> !', text, 1)
           )
         )
       ) eol
from posthistory ph
where posthistorytypeid in (2,5)
and (text like N'%>![^[]%' -- skip images
or   text like N'%> ![^[]%') -- allow space, skip images
and id = (select max(id) 
          from posthistory 
          where posthistorytypeid in (2,5)
          and postid = ph.postid)
union all
select ph.id
     , ph.postid
     , len(text) length
     , IIF(charindex('>!', text, ts.eol) > charindex('> !', text, ts.eol)
       , charindex('>!', text, ts.eol)
       , charindex('> !', text, ts.eol)) start
     , charindex(char(13) + char(10)
       , text
       , IIF(charindex('>!', text, ts.eol) > charindex('> !', text, ts.eol)
       , charindex('>!', text, ts.eol)
       , charindex('> !', text, ts.eol))) eol
from posthistory ph
inner join spoil ts on ts.id = ph.id 
                   and charindex('>!', ph.text, ts.eol) > 0
where charindex(char(13) + char(10)
       , text
       , IIF(charindex('>!', text, ts.eol) > charindex('> !', text, ts.eol)
         , charindex('>!', text, ts.eol)
         , charindex('> !', text, ts.eol))
      ) > 0
)

select postid as [Post Link]
     , (case when max(posttypeid) = 1 then 'Question' else 'Answer' end) [Q/A]  
     , max(length) [body length]
     , sum(eol-start) [spoiler length]
     , count(*) [number of spoiler blocks]
     , cast(sum(eol-start) as decimal) / max(length) * 100 [perc]
from spoil
inner join posts p on p.id = spoil.postid
group by postid, posttypeid 
order by cast(sum(eol-start) as decimal) / max(length) * 100 desc

fork query

edited aug 25 17

created aug 25 17 rene