Tag Question Timeline

0

Please login or register to vote for this query.

(click on this box to dismiss)

A timeline of how many questions were asked for a given tag since it was created.

Stack Overflow

Q&A for professional and enthusiast programmers

-- From http://stackoverflow.com/a/271878/422184
DECLARE @DateFrom smalldatetime, @DateTo smalldatetime;
SET @DateFrom=(
  -- Select oldest use of tag
  SELECT TOP 1
    cast(posts.CreationDate as date)
  FROM Posts posts
  JOIN PostTags pt
    ON pt.PostId = posts.Id
  JOIN Tags as tags
    ON pt.TagId = tags.Id
  WHERE tags.TagName = ##TagName:string##
  ORDER BY posts.CreationDate ASC
);
-------------------------------
WITH T(date)
AS
( 
SELECT cast(@DateFrom  as date)
UNION ALL
SELECT cast(DateAdd(day,1,T.date) as date) FROM T WHERE T.date <= getdate()
)
SELECT
  T.date,
  COALESCE(tag.Total, 0) AS 'Total'
FROM T 
LEFT JOIN (
  SELECT
    cast(
      posts.CreationDate as date
    ) as 'Day',
    COUNT(*) as 'Total'
  FROM PostTags as pt
  JOIN POSTS as posts
    ON pt.PostId = posts.Id
  JOIN Tags as tags
    ON pt.TagId = tags.Id
  WHERE tags.TagName = ##TagName:string##
  GROUP BY 
    cast(
      posts.CreationDate as date
    )
  ) tag
  ON tag.Day = T.Date
OPTION (MAXRECURSION 32767);

Enter Parameters

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