Comment Move Stats

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

-- Columns in output:
--    ModMoved*: Number of comment discussions moved by mods.
--    UserMoved*: Number of comment discussions moved by users.
--    ChatLinks*: Number of comments containing links to chat.
--    TotalComments: Total number of comments.
--    DeletedPosts: Total number of deleted posts.
--    TotalPosts: Total number of posts.
--    PresentPosts: Total number of posts that aren't deleted.
--    DeletePct: Fraction of posts that have been deleted.
--    ProjModMoved*: ModMoved corrected for deleted fraction.
--    ProjUserMoved*: UserMoved corrected for deleted fraction.
-- Columns marked with * are estimates, as good as we can get in a simple query.
-- Default date cutoff is August 5, 2015, when "move to comments" feature was
-- added to the mod control panel (https://meta.stackexchange.com/a/262154).

WITH MoveStats AS (
  SELECT
    COUNT(CASE WHEN Text LIKE '%/discussion-on-%' THEN 1 ELSE NULL END) ModMoved,
    COUNT(CASE WHEN Text LIKE '%/discussion-between-%' THEN 1 ELSE NULL END) UserMoved,
    COUNT(*) ChatLinks
  FROM
    Comments
  WHERE
    Text LIKE '%://chat.%'
    AND CreationDate >= ##DateCutoff:string?2015-08-05 00:00:00##
),

CommentStats AS (
  SELECT
    COUNT(*) TotalComments
  FROM
    Comments
  WHERE
    CreationDate >= ##DateCutoff:string##
),

PostStats AS (
  SELECT
    COUNT(CASE WHEN DeletionDate IS NOT NULL THEN 1 ELSE NULL END) DeletedPosts,
    COUNT(*) TotalPosts
  FROM
    PostsWithDeleted
  WHERE
    PostTypeId IN (1, 2)
    AND CreationDate >= ##DateCutoff:string##
)

SELECT
  *,
  ROUND(1.0 * ModMoved / (1.0 - DeletePct), 0) ProjModMoved,
  ROUND(1.0 * UserMoved / (1.0 - DeletePct), 0) ProjUserMoved
FROM (
  SELECT DISTINCT
    *,
    p.TotalPosts - p.DeletedPosts PresentPosts,
    1.0 * p.DeletedPosts / p.TotalPosts DeletePct
  FROM
    MoveStats, CommentStats, PostStats p
) x

Enter Parameters

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