List of questions sorted based on their spoiler markdown percentage

Question

On some entertainment sites (e.g. Anime.SE where I'm actively participating on), sometimes spoiler markdown is used to... hide spoilers.

While its usage is usually considerate, sometimes it's overused until there's nothing left shown normally on the post like this question.

^{(however, this meta post is not asking about the policy of overusing spoiler markdown)}

Is there a tool/SEDE query to get a list of questions based on their spoiler markdown percentage? For example, the linked question probably consists of around 90% spoiler block.

This might help to find questions that could probably be improved by reducing spoiler blocks to a considerate amount.

Answer 1

I wrote something up that comes close to what you want:

For every Question containing spoiler tags on Anime.SE, this query:

• Calculates the Post Length
• Calculates the Amount of Spoiler Chars in the Question
• Calculates the difference in Percent

It then lists off the questions sorted by highest % of the question being a spoiler.

Thanks to @rene I was able to rework the query to now also strip HTML tags (and be more performant thanks to cursors!):

DECLARE @qt_st INT;
DECLARE @sp_bdy NVARCHAR(MAX);
DECLARE @qt_bdy NVARCHAR(MAX);
DECLARE @cur_pst INT;
DECLARE @to_search INT;
 
-- first pass stripping HTML tags, adapted from https://stackoverflow.com/a/457725/
DECLARE @sta_string INT;
DECLARE @end_string INT;
DECLARE @len_string INT;
DECLARE @stripped_body NVARCHAR(MAX);
DECLARE @stripped_spoiler NVARCHAR(MAX);
 
 
-- at first, all posts containing spoilers are heaped into a temporary table, to improve performance
SELECT 
p.Id,
p.Body,
1 AS sp_amnt,
1 AS bdy_amnt,
p.CreationDate
 
INTO #temp_posts
 
FROM Posts AS p
 
WHERE ( CHARINDEX('<blockquote class="spoiler">', p.Body) > 0) AND ( p.PostTypeId = 1 );
 
 
-- iterates across every single post in the temporary table
-- second try using a cursor. Thanks to inspiration by @rene
 
DECLARE posts_cursor CURSOR
FOR
SELECT tmp.Id, tmp.Body -- tmp.bdy_amnt, tmp.sp_amnt
FROM #temp_posts AS tmp
FOR UPDATE OF tmp.bdy_amnt, tmp.sp_amnt;
 
OPEN posts_cursor;
 
FETCH FROM posts_cursor
INTO @cur_pst, @qt_bdy;
 
 
WHILE @@FETCH_STATUS = 0
  BEGIN
  
    SET @qt_st = 1;
    SET @to_search = 1;
    SET @sp_bdy=''
    WHILE @qt_st != 0 AND @qt_st IS NOT NULL
      BEGIN
            SET @qt_st = CHARINDEX('<blockquote class="spoiler">', @qt_bdy, @to_search);
            SET @to_search = @to_search + @qt_st;
        IF @to_search >= LEN(@qt_bdy)
            SET @qt_st = 0;
        IF @qt_st != 0 AND @qt_st IS NOT NULL
          SET @sp_bdy = @sp_bdy + SUBSTRING(@qt_bdy, @qt_st,CHARINDEX('</blockquote>', @qt_bdy, @qt_st) - @qt_st);
      END
    -- first pass attempting to strip HTML tags before processing, adapted from https://stackoverflow.com/a/457725/
    
    SET @stripped_body = @qt_bdy;
    SET @sta_string = CHARINDEX('<',@stripped_body)
    SET @end_string = CHARINDEX('>',@stripped_body,CHARINDEX('<',@stripped_body))
    SET @len_string = (@end_string - @sta_string) + 1
    WHILE @sta_string > 0 AND @end_string > 0 AND @len_string > 0
      BEGIN
          SET @stripped_body = STUFF(@stripped_body,@sta_string,@len_string,'');
          SET @sta_string = CHARINDEX('<',@stripped_body);
          SET @end_string = CHARINDEX('>',@stripped_body,CHARINDEX('<',@stripped_body));
          SET @len_string = (@end_string - @sta_string) + 1;
      END  
    
    --if (@sp_bdy is not null)
    --begin
    SET @stripped_spoiler = @sp_bdy;
    SET @sta_string = CHARINDEX('<',@stripped_spoiler)
    SET @end_string = CHARINDEX('>',@stripped_spoiler,CHARINDEX('<',@stripped_spoiler))
    SET @len_string = (@end_string - @sta_string) + 1
    WHILE @sta_string > 0 AND @end_string > 0 AND @len_string > 0
      BEGIN
          SET @stripped_spoiler = STUFF(@stripped_spoiler,@sta_string,@len_string,'');
          SET @sta_string = CHARINDEX('<',@stripped_spoiler);
          SET @end_string = CHARINDEX('>',@stripped_spoiler,CHARINDEX('<',@stripped_spoiler));
          SET @len_string = (@end_string - @sta_string) + 1;
      END  
      UPDATE #temp_posts SET sp_amnt = LEN(LTRIM(RTRIM(@stripped_spoiler))), bdy_amnt = LEN(LTRIM(RTRIM(@stripped_body))) WHERE CURRENT OF posts_cursor;
    --end
    FETCH FROM posts_cursor
    INTO @cur_pst, @qt_bdy;
  END
 
CLOSE posts_cursor;
DEALLOCATE posts_cursor;
 
SELECT
 
Id AS [Post Link],
CreationDate AS [Post Date],
bdy_amnt AS [Lenght of Post (chars)],
sp_amnt AS [Spoiler Content (chars)],
( sp_amnt * 100 / bdy_amnt ) AS [Percent Spoiler]
 
FROM #temp_posts
 
ORDER BY ( sp_amnt * 100 / bdy_amnt ) DESC;

Check it out on SEDE

Answer 2

The query from @Magisch is pretty impressive so let me offer an alternative, both in the source tables used as in the approach.

Where @Magisch uses the Post table, that contains the generated HTML you also find in a page, I decided to look at the actual markdown. That is what you see when you edit a post. The markdown is found in the posthistory table for rows with posthistorytypes 2 and 5 (initial/edit). It turns out that a spoiler starts at a new line and then can't have any line breaks. So a spoiler end at the next linebreak (a char(13) and char(10) in the implementation of SE).

Instead of doing a stored procedure I used a recursive Common Table Expression with the same goal as the SP, find all the spoilers in the same post.

Bringing this all together lead to this query where I tried to cater for cases where the spoiler markdown is >! and > !. On Anime.se no other variations exist.

I had hoped to stay clear of the posts table with this query but to meet the requirement to indicate if the post is a question or answer I had no other option then to join with the Posts table in the final query to obtain the posttypeid (1=Q, 2=A) for the post.

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

Check this example post why it is important to look at the last edit of the post.