0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the trend for your favourite tag

Q&A for professional and enthusiast programmers

DECLARE @TagName varchar(255) = '##TagName##';

DECLARE @PostCountByMonth TABLE
(
  [date] datetime,
  [totalCount] int,
  [##TagName##Count] int,
  UNIQUE CLUSTERED ([date])
);

INSERT INTO @PostCountByMonth
SELECT dateadd(month, DATEDIFF(month, '', p.CreationDate), '') [date],
      count(*) [totalCount],
      count(p.[IsTag]) [##TagName##Count]
FROM
  (
    SELECT Posts.CreationDate, (case when Posts.Tags = '<##TagName##>' then 1 else null end) [IsTag]
    FROM Posts
    WHERE Posts.PostTypeId = 1
  ) p
GROUP BY DATEDIFF(month, '', p.CreationDate)
ORDER BY DATEDIFF(month, '', p.CreationDate);

SELECT p1.[date], sum(p1.[##TagName##Count]) * 100.0 / sum(p1.[totalCount]) [Percentage of questions tagged ##TagName##]
FROM @PostCountByMonth p1 
GROUP BY p1.[date]

--SELECT p1.[date], sum(p2.[##TagName##Count]) * 100.0 / sum(p2.[totalCount]) [Percentage of questions tagged ##TagName##]
--FROM @PostCountByMonth p1 INNER JOIN @PostCountByMonth p2
--  ON p1.[date] = p2.[date]
--GROUP BY p1.[date]

fork query

edited jun 9 14 sethladd

created dec 13 10 prof3ta