11

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.

  • I don't think there's anything ready but I'd be happy to chuck a bounty on this to attract the attention of SEDE wizards if you don't get a good answer. Please ping me when this is eligible for a bounty in that case. Thanks (-: – tripleee Aug 8 '17 at 5:35
  • I gave it a shot but for some reason only very few posts show up: data.stackexchange.com/anime/query/714557/… – Magisch Aug 25 '17 at 11:07
7
+250

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

  • @Elias I hazard a guess it's because my script doesn't count the actual spoiler blockquote tags in the body as spoilers, but only the text in between them – Magisch Aug 25 '17 at 12:57
  • Ah, that makes sense. The Python script strips all HTML tags before calculating the percentage because they are not shown in the rendered version, but I'm not sure stripping them in SQL is an easy task... – No Distraction Wizard Aug 25 '17 at 13:00
  • @Elias It would probably take a whole nother layer of processing and would lead to some messed up calcs along the way. Probably possible, but I'm kind of reluctant to spend another hour on this to dent out a couple % give or take. – Magisch Aug 25 '17 at 13:02
5
+250

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.

  • Listing all posts is okay, but is it possible to add PostType to clearly indicate whether it's a question or an answer? – No Distraction Wizard Aug 25 '17 at 18:23
  • @Elias I had hoped to stay clear of the posts table – rene Aug 25 '17 at 18:36
  • @Elias I joined with posts in the last query, that gives you the q/a indication – rene Aug 25 '17 at 18:48
  • Thanks!.. but I just noticed you can get negative spoiler length and percentage :( – No Distraction Wizard Aug 25 '17 at 18:51
  • 1
    Yes, great, isn't it! – rene Aug 25 '17 at 18:52
  • @Elias parsing in SQL is no fun. Bug is fixed. – rene Aug 25 '17 at 19:08
  • 1
    @Magisch gracefully suggested I should award the bounty to this answer, so I have now done that. Thanks to both of you! – tripleee Aug 28 '17 at 12:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .