My Score by UTC Hour


Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

Q&A about the site for members of the workforce navigating the professional setting

-- My Score by UTC Hour
-- What time of day do I earn the most scores from questions and answers?

DECLARE @UserID INT = ##UserID?9449426##

-- Create a table containing all the hours in a day (0-24)
SELECT  x.number AS Hours
INTO    #Hours
FROM    master.dbo.spt_values x
WHERE   x.type = 'P'        
AND     x.number <= 23;

-- Get all questions and answers from this user
INTO #MyPosts
FROM Posts
  PostTypeId IN (1, 2) AND
  OwnerUserId = @UserID

  SUM(CASE WHEN PostTypeId = 1 THEN p.Score ELSE 0 END) AS Questions,
  SUM(CASE WHEN PostTypeId = 2 THEN p.Score 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

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results