0

Please login or register to vote for this query.

(click on this box to dismiss)

Number of questions posted to the site per month, plotted by month. "# Questions w/Deleted" counts all questions posted, including those that have since been deleted. "# Non-Deleted Questions" counts just those that remain un-deleted. "# Deleted Questions" is a plot of the difference between these two curves. "# Closed Questions" plots a count of the subset of **un-deleted questions** that are closed.

Q&A for Ubuntu users and developers

SELECT MIN(pwd.CreationDate), --DATEDIFF(month, CURRENT_TIMESTAMP, MAX(p.CreationDate)) AS 'Months Ago',
      COUNT((CAST(YEAR(pwd.CreationDate) as nvarchar(4)) + '-' + CAST(MONTH(pwd.CreationDate) as nvarchar(2)))) AS '# Questions w/Deleted',
      COUNT((CAST(YEAR(p.CreationDate) as nvarchar(4)) + '-' + CAST(MONTH(p.CreationDate) as nvarchar(2)))) AS '# Non-Deleted Questions',
      COUNT((CAST(YEAR(pwd.CreationDate) as nvarchar(4)) + '-' + CAST(MONTH(pwd.CreationDate) as nvarchar(2)))) - COUNT((CAST(YEAR(p.CreationDate) as nvarchar(4)) + '-' + CAST(MONTH(p.CreationDate) as nvarchar(2)))) AS '# Deleted Questions',
      COUNT((CAST(YEAR(pc.CreationDate) as nvarchar(4)) + '-' + CAST(MONTH(pc.CreationDate) as nvarchar(2)))) AS '# Closed Questions'

FROM PostsWithDeleted pwd
LEFT JOIN Posts p ON pwd.Id = p.Id
LEFT JOIN Posts pc ON pwd.Id = pc.Id AND pc.ClosedDate IS NOT NULL

WHERE pwd.PostTypeId = 1

GROUP BY (CAST(YEAR(pwd.CreationDate) as nvarchar(4)) + '-' + CAST(MONTH(pwd.CreationDate) as nvarchar(2)))

ORDER BY MIN(pwd.CreationDate) ASC

fork query

edited nov 24 17 anonymous

created apr 6 17 hBy2Py