What time of day do I post questions and answers most?
Q&A about the site for developers and users of elementary OS and applications
-- My Activity by UTC Hour
-- What time of day do I post questions and answers most?
DECLARE @UserID INT = ##UserID?9449426##
-- Create a table containing all the hours in a day (0-24)
SELECT x.number AS Hours
FROM master.dbo.spt_values x
WHERE x.type = 'P'
AND x.number <= 23;
-- Get all questions and answers from this user
PostTypeId IN (1, 2) AND
OwnerUserId = @UserID
SUM(CASE WHEN PostTypeId = 1 THEN 1 ELSE 0 END) as Questions,
SUM(CASE WHEN PostTypeId = 2 THEN 1 ELSE 0 END) as Answers
FROM #Hours h
LEFT JOIN #MyPosts p ON datepart(hour, CreationDate) = h.Hours
GROUP BY h.Hours
ORDER BY h.Hours