Suspicious question deletions from the last week

3

Please login or register to vote for this query.

(click on this box to dismiss)

Lists the questions deleted in the last week, ordered by the sum of their score + the sum of the score of their positively scored answers. On the top of the list might be unfair question deletions! Citing the relevant directive: *"Before voting to delete, please check whether there are any good answers; [...] We don't like to lose great answers!"*

Ask Ubuntu

Q&A for Ubuntu users and developers

DECLARE @From DATETIME = CASE
  WHEN ##From:string?last week## = 'last week'
  THEN (SELECT DATEADD(d, -7, MAX(CreationDate)) FROM PostHistory)
  ELSE ##From##
  END;

DECLARE @Until DATETIME = CASE
  WHEN ##Until:string?last backup## = 'last backup'
  THEN (SELECT MAX(CreationDate) FROM PostHistory)
  ELSE ##Until##
  END;

DECLARE @Site VARCHAR(MAX);
SELECT @Site =
  LOWER(CASE
    WHEN                                                                                                                                                                                    
      DB_NAME() = 'StackExchange.Programmers'                                                                                                                                                    
    THEN                                                                                                                                                                                    
      'softwareengineering.stackexchange.com'                                                                                                                                               
    WHEN                                                                                                                                                                                    
      DB_NAME() = 'StackExchange.Programmers.Meta'                                                                                                                                               
    THEN                                                                                                                                                                                    
      'softwareengineering.meta.stackexchange.com'                                                                                                                                          
    WHEN                                                                                                                                                                                    
      DB_NAME() LIKE 'StackExchange.%'                                                                                                                                                           
    THEN                                                                                                                                                                                    
      RIGHT(DB_NAME(), LEN(DB_NAME()) - 14) + '.stackexchange.com'                                                                                                                                    
    WHEN                                                                                                                                                                                    
      DB_NAME() = 'StackOverflow.Br'                                                                                                                                                             
    THEN                                                                                                                                                                                    
      'pt.stackoverflow.com'                                                                                                                                                                
    WHEN                                                                                                                                                                                    
      DB_NAME() = 'StackOverflow.Br.Meta'                                                                                                                                                        
    THEN                                                                                                                                                                                    
      'pt.meta.stackoverflow.com'                                                                                                                                                           
    WHEN                                                                                                                                                                                    
      DB_NAME() LIKE 'StackOverflow.__'                                                                                                                                                          
    THEN                                                                                                                                                                                    
      RIGHT(DB_NAME(), 2) + '.stackoverflow.com'                                                                                                                                                 
    WHEN                                                                                                                                                                                    
      DB_NAME() LIKE 'StackOverflow.__.Meta'
    THEN
      RIGHT(DB_NAME(), 7) + '.stackoverflow.com'
    WHEN
      DB_NAME() = 'StackOverflow'
    THEN
      'stackoverflow.com'
    WHEN
      DB_NAME() = 'StackOverflow.Meta'
    THEN
      'meta.stackoverflow.com'
    WHEN
      DB_NAME() = 'StackApps'
    THEN
      'stackapps.com'
    WHEN
      DB_NAME() = 'ServerFault'
    THEN
      'serverfault.com'
    WHEN
      DB_NAME() = 'ServerFault.Meta'
    THEN
      'meta.serverfault.com'
    WHEN
      DB_NAME() = 'SuperUser'
    THEN
      'superuser.com'
    WHEN
      DB_NAME() = 'SuperUser.Meta'
    THEN
      'meta.superuser.com'
    ELSE
      NULL
    END
  );

WITH Raw AS (
  SELECT
    Q.Id,
    MIN(Q.Score) AS QScore,
    MIN(Q.Score) + SUM(CASE WHEN A.Score > 0 THEN A.Score ELSE 0 END)
      AS SumScore,
    COUNT(CASE WHEN A.Score > 0 THEN 1 ELSE NULL END) AS UpvotedACount
  FROM PostsWithDeleted Q, PostsWithDeleted A
  WHERE
    A.ParentId = Q.Id
    AND
    Q.DeletionDate BETWEEN @From AND @Until
  GROUP BY Q.Id
)
SELECT
  Raw.SumScore,
  Raw.UpvotedACount,
  Raw.Id,
  PostsWithDeleted.Score,
  PostsWithDeleted.CreationDate,
  PostsWithDeleted.ClosedDate,
  PostsWithDeleted.DeletionDate,
  'site://questions/' + CAST(PostsWithDeleted.Id AS VARCHAR(MAX))
    + '|Question' AS QuestionLink,
  'query://1057592?QuestionId=' + CAST(PostsWithDeleted.Id AS VARCHAR(MAX))
    + '#resultSets|Recover' AS [Recover data],
  'https://web.archive.org/web/%2A/https://' + @Site + '/questions/' +
    CAST(Raw.Id AS VARCHAR(MAX)) + '/%2A|Recover content' AS [Archive.org]
FROM PostsWithDeleted, Raw
WHERE
  PostsWithDeleted.Id = Raw.Id
  AND
  Raw.QScore > 0
  AND
  Raw.UpvotedACount > 0
ORDER BY Raw.SumScore DESC, Raw.Id

Enter Parameters

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