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 (
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,
Text LIKE '%://chat.%'
AND CreationDate >= ##DateCutoff:string?2015-08-05 00:00:00##
CommentStats AS (
CreationDate >= ##DateCutoff:string##
PostStats AS (
COUNT(CASE WHEN DeletionDate IS NOT NULL THEN 1 ELSE NULL END) DeletedPosts,
PostTypeId IN (1, 2)
AND CreationDate >= ##DateCutoff:string##
ROUND(1.0 * ModMoved / (1.0 - DeletePct), 0) ProjModMoved,
ROUND(1.0 * UserMoved / (1.0 - DeletePct), 0) ProjUserMoved
p.TotalPosts - p.DeletedPosts PresentPosts,
1.0 * p.DeletedPosts / p.TotalPosts DeletePct
MoveStats, CommentStats, PostStats p