Tag activity by UTC hour in the last 3 months

1

Please login or register to vote for this query.

(click on this box to dismiss)

Martial Arts Meta

Q&A about the site for students and teachers of all martial arts

-- This query help me to find the most profitable hours to farm rep
-- because I can't be awake for 24 hours.

DECLARE @TagName Nvarchar(Max) = '##TagName?reactjs##'
-- LastMonth: How many months back to start calculating. Default is the last 3 months
DECLARE @LastMonth Int = ##LastMonth?3##
DECLARE @LastnMonths DateTime = DATEADD(Month, -@LastMonth, GETDATE())
DECLARE @MinDate DateTime = dateadd(Month, datediff(Month, 0, @LastnMonths), 0)

-- 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 question activity in this tag
SELECT
  h.Hours,
  COUNT(q.Id)  Questions,
  SUM(q.Score) Question
INTO #Questions
FROM #Hours h
LEFT JOIN Posts q     ON datepart(hour, q.CreationDate) = h.Hours
     JOIN PostTags pt ON pt.PostId = q.Id
     JOIN Tags t      ON pt.TagId  = t.Id
WHERE t.TagName = @TagName
  AND q.PostTypeId = 1
  AND q.CreationDate >= @MinDate
GROUP BY h.Hours

-- Get answer activity in this tag
SELECT
  h.Hours,
  COUNT(a.Id)  Answers,
  SUM(a.Score) Answer
INTO #Answers
FROM #Hours h
LEFT JOIN Posts q     ON datepart(hour, q.CreationDate) = h.Hours
     JOIN PostTags pt ON pt.PostId = q.Id
     JOIN Tags t      ON pt.TagId  = t.Id
LEFT JOIN Posts a     ON q.Id      = a.ParentId
WHERE t.TagName = @TagName
  AND q.PostTypeId = 1
  AND a.PostTypeId = 2
  AND q.CreationDate >= @MinDate
GROUP BY h.Hours

SELECT
  q.Hours,
  Questions QuestionCount,
  Question QuestionScore,
  Answers AnswerCount,
  Answer AnswerScore
FROM #Questions q
JOIN #Answers a ON q.Hours = a.Hours
GROUP BY q.Hours, Question, Questions, Answer, Answers
ORDER BY q.Hours

Enter Parameters

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