0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for scientists using computers to solve scientific problems

SELECT
	CONVERT(Date, Questions.ClosedDate) AS Date,
	COUNT(DISTINCT qIn.Id) AS QuestionsIn,
	COUNT(DISTINCT Questions.Id) QuestionsOut
FROM Posts Questions
	INNER JOIN (
		SELECT
			p.Id,
			MIN(v.CreationDate) AS FirstCloseVoteDate
		FROM Posts p
			INNER JOIN Votes v
			ON V.PostId = p.Id
			AND v.VoteTypeId = 6 -- Close
		WHERE p.CreationDate >= '2013-08-01' 
            AND p.CreationDate < '2013-09-10'
		GROUP BY p.Id
	) qIn
	ON qIn.FirstCloseVoteDate = CONVERT(Date, Questions.ClosedDate)
WHERE Questions.CreationDate >= '2013-08-01' 
    AND Questions.CreationDate < '2013-09-10'
    AND Questions.ClosedDate IS NOT NULL
GROUP BY CONVERT(Date, Questions.ClosedDate)
ORDER BY CONVERT(Date, Questions.ClosedDate)

fork query

edited sep 11 13 anonymous

created dec 23 12 bfavaretto