Tag badge progress graph - number of a user's answers in a tag over time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH DatesOfChanges(Id,ChangeDate) AS (

SELECT
questions.Id,
CASE WHEN answers.CreationDate > MIN(ph.CreationDate) 
     THEN answers.CreationDate
     ELSE MIN(ph.CreationDate) 
     END AS ChangeDate
FROM Posts answers
JOIN Posts questions ON questions.Id = answers.ParentId
JOIN PostHistory ph ON ph.PostId = questions.Id
WHERE answers.OwnerUserId = ##UserId##
  AND questions.Tags LIKE '%<'+##Tag##+'>%'
  AND ph.Text LIKE '%<'+##Tag##+'>%'
  AND ph.PostHistoryTypeId IN (3,6) --initial tags, edit tags
  AND ##DummyDate:string? ## = ##DummyDate:string? ##
GROUP BY questions.Id, questions.CreationDate, answers.CreationDate

)

SELECT 
  ChangeDate,
  ROW_NUMBER() OVER (ORDER BY ChangeDate) AS Number
FROM DatesOfChanges
ORDER BY ChangeDate

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results