Satisfaction with moderators and system: Amount of new posts deleted by moderators or the system

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT Month, 'Satisfaction with moderators and system' = 1 - [Amount of new posts deleted by moderators or the system]
FROM (
      SELECT nr_posts.Month, 
             'Amount of new posts deleted by moderators or the system' = nr_deleted.[Amount of posts deleted by moderators or the system] / CAST(nr_posts.Posts AS FLOAT)
      FROM

      (SELECT Month = DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), Posts = COUNT(id)
      FROM Posts
      GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)) AS nr_posts

      INNER JOIN
      (SELECT Month = DATEADD(MONTH, DATEDIFF(MONTH, 0, PostNotices.DeletionDate), 0),
             'Amount of posts deleted by moderators or the system' = COUNT(*)
      FROM Posts, PostNotices
      WHERE PostNotices.DeletionDate IS NOT NULL
            AND DeletionUserId != PostNotices.OwnerUserId
            AND PostNotices.PostId = Posts.Id
            AND DATEDIFF(MONTH, Posts.CreationDate, PostNotices.DeletionDate) < 1
            GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, PostNotices.DeletionDate), 0)) AS nr_deleted
      ON nr_posts.Month = nr_deleted.Month
     ) AS Procedures
ORDER BY Month

Enter Parameters

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