0

Please login or register to vote for this query.

(click on this box to dismiss)

Preguntas y respuestas para programadores y profesionales de la informática

;WITH hourgenerator																-- <<== generates 0-23 (24Hour)
AS
(
    SELECT 0 AS hourPosted UNION ALL
    SELECT 	hourPosted + 1 
	FROM 	hourgenerator
	WHERE 	hourPosted < 23
),
questionsPerHour
AS
(
	SELECT 	CAST(p.CreationDate as DATE) AS [Date],
			DATEPART(Hour, p.CreationDate) AS hourPosted,
			COUNT(*) NumberOfQuestions,
			AVG(DATEDIFF(second, p.CreationDate, pa.CreationDate)) AvgTimeAnswered
	FROM 	Posts AS p
			INNER JOIN Posts As pa
				ON	pa.PostTypeId = 2											-- <<== Answer
					AND p.Id = pa.ParentID
					AND	p.OwnerUserId <> pa.OwnerUserId 						-- <<== not come on the same poster
					AND	p.AcceptedAnswerId = pa.Id
	WHERE 	p.PostTypeId = 1 													-- <<== Questions
			AND p.CommunityOwnedDate IS NULL  									-- <<== not WIKI
			AND DATEDIFF(second, p.CreationDate, pa.CreationDate) >= 0
			AND p.OwnerUserId IS NOT NULL										-- <<== user is not deleted
			AND pa.OwnerUserId IS NOT NULL										-- <<== user is not deleted
			AND p.CreationDate >= CAST('2013-11-14' AS DATE)
			AND p.CreationDate < DATEADD (dd, 1 ,CAST('2013-11-14' AS DATE))
	GROUP	By CAST(p.CreationDate as DATE), DATEPART(Hour, p.CreationDate)
)
SELECT	COALESCE(qph.[Date], CAST('2013-11-14' AS DATE)) AS [Date],
		hg.hourPosted,
		COALESCE(qph.NumberOfQuestions, 0) AS NumberOfQuestions,
		COALESCE(qph.AvgTimeAnswered, 0) AS AvgTimeAnswered
FROM	hourgenerator AS hg
		LEFT JOIN questionsPerHour AS qph
			ON 	hg.hourPosted = qph.hourPosted
ORDER	BY hg.hourPosted

fork query

edited feb 3 14

created feb 3 14 anonymous