popular questions by time of day and day of week

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

SELECT  DATEPART(dw, A.CreationDate) [DayOfWeek],
        DATENAME(dw, A.CreationDate) [Day],
        RIGHT('0' + CAST(DATEPART(hh, A.CreationDate) AS VARCHAR(2)), 2) 
          + ':00 - '  
          + RIGHT('0' + CAST(DATEPART(hh, A.CreationDate) AS VARCHAR(2)), 2)
          + ':59' [Hour],
        AVG(CAST(A.Score AS DECIMAL)) AverageScore
FROM    Posts Q
        INNER JOIN Posts A ON Q.Id = A.ParentId
WHERE   -- questions
        Q.PostTypeId = 1 
        -- answers
        AND A.PostTypeId = 2 
        -- high score
        AND A.Score > 10
        -- asked in last 3 months        
        AND Q.CreationDate > DATEADD(m, -12, GETUTCDATE())
GROUP BY DATEPART(dw, A.CreationDate),
        DATENAME(dw, A.CreationDate),
        DATEPART(hh, A.CreationDate)
ORDER BY [DayOfWeek], 
        [Hour];

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached